Solved

LEFT JOIN Help

Posted on 2008-10-05
5
296 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL for monthly balance change 15 61
SQL query 4 27
total hours between two times grouped by type 6 29
simple mysql statement 3 0
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now