asked on

LEFT JOIN Help

Hi,

I would like to include NULL values in some of the joins;
Here is my query:

SELECT R1.nom_fr, R1.nom_nl, P1.nom_fr, P1.nom_nl, C1.nom_fr, C1.nom_nl, S1.nom_fr, S1.nom_nl,
R2.nom_fr, R2.nom_nl, P2.nom_fr, P2.nom_nl, C2.nom_fr, C2.nom_nl, S2.nom_fr, S2.nom_nl,
passengers as seats, departure, member_id, record_date
FROM passagers P, regions R1, regions R2, provinces P1, provinces P2, communes C1, communes C2, sections S1, sections S2
WHERE P.to_delete = 0
AND R1.id = P.region_departure
AND R2.id = P.region_arrival
AND P1.id = P.province_departure // may be NULL
AND P2.id = P.province_arrival // may be NULL
AND C1.id = P.commune_departure
AND C2.id = P.commune_arrival
AND S1.id = P.section_departure // may be NULL
AND S2.id = P.section_arrival // may be NULL

Also if there is any way I can optimize, tips are welcome;
Thank you for any help.

Kevin Cross

I would explicitly define the JOIN types in the FROM instead of WHERE clause.

FROM passagers P, regions R1, regions R2, provinces P1, provinces P2, communes C1, communes C2, sections S1, sections S2

To this:

SELECT R1.nom_fr, R1.nom_nl, P1.nom_fr, P1.nom_nl, C1.nom_fr, C1.nom_nl, S1.nom_fr, S1.nom_nl,
R2.nom_fr, R2.nom_nl, P2.nom_fr, P2.nom_nl, C2.nom_fr, C2.nom_nl, S2.nom_fr, S2.nom_nl,
passengers as seats, departure, member_id, record_date
FROM (passagers P INNER JOIN regions R1 ON R1.id = P.region_departure
INNER JOIN regions R2 ON R2.id = P.region_arrival
INNER JOIN communes C1 ON C1.id = P.commune_departure
INNER JOIN communes C2 ON C2.id = P.commune_arrival)
LEFT JOIN provinces P1 ON P1.id = P.province_departure   
LEFT JOIN provinces P2 ON P2.id = P.province_arrival   
LEFT JOIN sections S1 ON S1.id = P.section_departure 
LEFT JOIN sections S2 ON S2.id = P.section_arrival
WHERE P.to_delete = 0

Open in new window

JimBrandley

This should do it for you.

SELECT R1.nom_fr, R1.nom_nl, P1.nom_fr, P1.nom_nl, C1.nom_fr, C1.nom_nl, S1.nom_fr, S1.nom_nl,
R2.nom_fr, R2.nom_nl, P2.nom_fr, P2.nom_nl, C2.nom_fr, C2.nom_nl, S2.nom_fr, S2.nom_nl,
passengers as seats, departure, member_id, record_date
FROM passagers P, regions R1, regions R2, provinces P1, provinces P2, communes C1, communes C2, sections S1, sections S2
WHERE P.to_delete = 0
AND R1.id = P.region_departure
AND R2.id = P.region_arrival
AND (P1.id = P.province_departure OR P.province_departure IS NULL)
AND (P2.id = P.province_arrival OR P.province_arrival IS NULL)
AND C1.id = P.commune_departure
AND C2.id = P.commune_arrival
AND (S1.id = P.section_departure OR P.section_departure IS NULL)
AND (S2.id = P.section_arrival OR P.section_arrival IS NULL)

Jim

vogelbekdier

ASKER

I want first to test the solution of Jim,

But it gives me an infinite number of records (the same single record repeated), it's like a loop, had to kill MySQL Query process ...

What is wrong with this query ?

Thank you.

SOLUTION

dportas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial