Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

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.
0
vogelbekdier
Asked:
vogelbekdier
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
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

0
 
JimBrandleyCommented:
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
0
 
vogelbekdierAuthor Commented:
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.
0
 
dportasCommented:
Jim's query is not a left outer join. The OR expressions join EVERY row in one table to every row with a NULL in the other. Not what you want I think.

Use the LEFT JOIN solution.
0
 
Kevin CrossChief Technology OfficerCommented:
Did you try my suggestion?  Not sure if my query is causing loop and so you want to test Jim's or you are saying Jim's did that.

The problem is when allowing NULL in Join then you can get unintended matches from other table.  If id has to match that table OR as long as that table is NULL it matches everything in first table...probably not what you want.  Using a left join, you will get all the records from first table regardless of a match thus getting you NULL or value.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now