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

x
?
Solved

SQL conditional join (mySQL)

Posted on 2014-04-22
5
Medium Priority
?
1,500 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
  • 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 2000 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

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.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month11 days, 18 hours left to enroll

916 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