Solved

LEFT JOIN Help

Posted on 2008-10-05
5
299 Views
Last Modified: 2012-05-05
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
Comment
Question by:vogelbekdier
5 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22646466
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
 
LVL 22

Expert Comment

by:JimBrandley
ID: 22646475
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
 

Author Comment

by:vogelbekdier
ID: 22648759
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
 
LVL 22

Assisted Solution

by:dportas
dportas earned 20 total points
ID: 22649110
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 105 total points
ID: 22649124
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question