Description:
I have a person and an address table, joined with a link table.
Address may contain different type of addresses according to the
addrtype field. I'd like to see a person with his/her different type
of addresses in one record.
I've tested this query with both MySQL 4.1.1 and MySQL 5.0.0 and they
resulted the same (wrong) result set:
id id id id
1 NULL 2 NULL
1 NULL NULL NULL
1 NULL 2 3
1 NULL NULL 3
I've also tested exactly the same query with Firebird, Postgres and
Oracle and it works find on those DBs. The correct result should be:
id id id id
1 NULL 2 3
Best wishes,
Daniel Marczisovszky
SQL script:
DROP TABLE IF EXISTS person;
DROP TABLE IF EXISTS address;
DROP TABLE IF EXISTS person_address;
CREATE TABLE person (id INT, name VARCHAR(50));
CREATE TABLE address (id INT, street VARCHAR(50), addrtype VARCHAR(50));
CREATE TABLE person_address (person_id INT, address_id INT);
INSERT INTO person VALUES (1, 'john the ripper');
INSERT INTO address VALUES (2, 'street 1', 'billing');
INSERT INTO address VALUES (3, 'street 2', 'post');
INSERT INTO person_address VALUES (1, 2);
INSERT INTO person_address VALUES (1, 3);
SELECT DISTINCT person.id, address1.id, address2.id, address3.id FROM person
LEFT JOIN
(person_address person_address1 INNER JOIN address address1
ON address1.id=person_address1.address_id)
ON person.id=person_address1.person_id AND (address1.addrtype = 'home')
LEFT JOIN
(person_address person_address2 INNER JOIN address address2
ON address2.id=person_address2.address_id)
ON person.id=person_address2.person_id AND (address2.addrtype = 'billing')
LEFT JOIN
(person_address person_address3 INNER JOIN address address3
ON address3.id=person_address3.address_id)
ON person.id=person_address3.person_id AND (address3.addrtype = 'post');
How to repeat:
See above.
Description: I have a person and an address table, joined with a link table. Address may contain different type of addresses according to the addrtype field. I'd like to see a person with his/her different type of addresses in one record. I've tested this query with both MySQL 4.1.1 and MySQL 5.0.0 and they resulted the same (wrong) result set: id id id id 1 NULL 2 NULL 1 NULL NULL NULL 1 NULL 2 3 1 NULL NULL 3 I've also tested exactly the same query with Firebird, Postgres and Oracle and it works find on those DBs. The correct result should be: id id id id 1 NULL 2 3 Best wishes, Daniel Marczisovszky SQL script: DROP TABLE IF EXISTS person; DROP TABLE IF EXISTS address; DROP TABLE IF EXISTS person_address; CREATE TABLE person (id INT, name VARCHAR(50)); CREATE TABLE address (id INT, street VARCHAR(50), addrtype VARCHAR(50)); CREATE TABLE person_address (person_id INT, address_id INT); INSERT INTO person VALUES (1, 'john the ripper'); INSERT INTO address VALUES (2, 'street 1', 'billing'); INSERT INTO address VALUES (3, 'street 2', 'post'); INSERT INTO person_address VALUES (1, 2); INSERT INTO person_address VALUES (1, 3); SELECT DISTINCT person.id, address1.id, address2.id, address3.id FROM person LEFT JOIN (person_address person_address1 INNER JOIN address address1 ON address1.id=person_address1.address_id) ON person.id=person_address1.person_id AND (address1.addrtype = 'home') LEFT JOIN (person_address person_address2 INNER JOIN address address2 ON address2.id=person_address2.address_id) ON person.id=person_address2.person_id AND (address2.addrtype = 'billing') LEFT JOIN (person_address person_address3 INNER JOIN address address3 ON address3.id=person_address3.address_id) ON person.id=person_address3.person_id AND (address3.addrtype = 'post'); How to repeat: See above.