Hello everyone,
can somebody explain me this - I have 3 tables:
PERSON: master table
ADDRESS: 1-M relationship
ID_Address
| ID1_FK
| Street_address
|
|---|
| 6 | 2nd avenue |
| 6 | 4th avenue |
OWNEDCARS: M-M relationship
ID_OwnedCars
| ID1_FK
| ID_OtherJoinedTable_FK
| Car_Model
|
|---|
| 6 | 100 | BMW |
| 6 | 100 | Mercedes |
Now let's say 1 person has 2 addresses and 2 owned cars. And when I do JOIN over all for specific record like this:
SELECT Name, Street_Address, Car_Model FROM PERSON
LEFT JOIN ADDRESS ON PERSON.ID1=ADDRESS.ID1_FK
LEFT JOIN OWNEDCARS ON PERSON.ID1=OWNDECARS.ID1_FK
WHERE PERSON.ID='6';
I get a result like this:
Mike, 4th avenue
Mike, 2nd avenue
Mike, BMW
Mike,BMW
Mike,Mercedes
Mike,Mercedes
As you notice, cars that were returned are doubled. Why is JOIN returning correct number of addresess but not cars ?
What I want is to get Distinct records from all joined table which regards to certain record.
P.S: notice that I have same name for ID's in "Address" and "OwnedCars" table, which is "ID1_FK". That is my actual design, so I'm afraid that It has to do something with name conflicts.
Thanks for help in advance !