Solved

LEFT JOIN Help

Posted on 2008-10-05
5
301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 60

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 60

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

623 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