• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1567
  • Last Modified:

SQL conditional join (mySQL)

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
kbit
Asked:
kbit
  • 2
  • 2
1 Solution
 
Ess KayEntrapenuerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Ess KayEntrapenuerCommented:
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
 
kbitAuthor Commented:
Thanks very much all, the only one I got working using my tables is Guy's solution.
0
 
kbitAuthor Commented:
Tremendous and complete answer as always, thank you
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now