Solved

SQL conditional join (mySQL)

Posted on 2014-04-22
5
1,370 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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 …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

763 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