Hi all,

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')