Solved

SQL conditional join (mySQL)

Posted on 2014-04-22
5
1,428 Views
Last Modified: 2014-04-22
Hi,

I have some tables in mySQL, lets say table1,table2,table3

I want to select records from table1 and left join them onto table2.
If a match is found on table2, I then need to do an inner join between table2 and table3 with a condition.

If I write the SQL like this:

SELECT * FROM table1 
LEFT JOIN table2 ON table1.id=table2.id 
LEFT JOIN table3 ON table2.id=table3.id AND otherid=2000

Open in new window

then the number of results is more than expected (duplicates).

If I do this,

SELECT * FROM table1 
LEFT JOIN table2 ON table1.id=table2.id 
LEFT JOIN table3 ON table2.id=table3.id
WHERE table3.otherid=2000

Open in new window

or this

SELECT * FROM table1 
LEFT JOIN table2 ON table1.id=table2.id 
INNER JOIN table3 ON table2.id=table3.id AND otherid=2000

Open in new window

the number of records is less than expected, because the "otherid=2000" is being applied to all records, not just the ones that are on both table1 and table2.

Can anyone please advise how I can go about this?

Many thanks
0
Comment
Question by:kbit
[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
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 40014874
SELECT DISTINCT * FROM table1
LEFT JOIN table2 ON table1.id=table2.id
INNER JOIN table3 ON table2.id=table3.id
--WHERE table3.otherid=2000
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40014879
you need to join table2 and table3 indeed like this:
SELECT t1.*, sq.* 
FROM table1 t1
LEFT JOIN ( select t2.* , t3.*
     FROM table2 t2
    JOIN table3 t3
        ON t2.id= t3.id AND t3.otherid=2000
 ) sq ON sq.id=table2.id  

Open in new window


the above will eventually error out if table2 and table3 have identical column names, in that case you need to replace the * by a concrete column list

hope this helps
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 40014888
THIS should do the following:
Select everything from table 1
JOIN on table 2 and 3 if a table3 record exists with otherid of 2000

SELECT DISTINCT * FROM table1
LEFT JOIN table2 ON table1.id=table2.id
INNER JOIN table3 ON table2.id=table3.id
AND table3.otherid=2000
0
 

Author Comment

by:kbit
ID: 40014977
Thanks very much all, the only one I got working using my tables is Guy's solution.
0
 

Author Closing Comment

by:kbit
ID: 40014980
Tremendous and complete answer as always, thank you
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

632 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