Solved

SQL conditional join (mySQL)

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

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mySQL Syntax 7 34
Create a Select Query and Populate a Table 3 43
grouping logic 6 46
Extract string portion 2 12
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.​
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

919 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

21 Experts available now in Live!

Get 1:1 Help Now