CODEWITHSUNDEEP
mysql
Sonia Bhatia
Sonia Bhatia

Reputation: 146

How to get results from join only if all records match

I am having a problem to make an SQL query. I have two tables named 'loans' and 'loan_emilists'

loan table

loan_emitlist table

I want to fetch loan id from first table i.e 'loans' where loan.emilists table status have all 1.

For example:-

Loan id = 1 Because in the 2nd table i.e. 'loan_emilists' loan_id = 1 have all status value 1.

MySQL Query:-

SELECT DISTINCT(loans.id) FROM loans LEFT JOIN loan_emilists ON loans.id = loan_emilists.loan_id WHERE loan_emilists.status = 1

But it gives me both 1 and 2 loan.id.

Upvotes: 1

Views: 2695

Answers (1)

sagi
sagi

Reputation: 40481

You can use not exists() :

SELECT s.id FROM loans s
WHERE NOT EXISTS(select 1 from loan_emilists t
                 where s.id = t.loan_id 
                   and t.status = 0)

This will work in case there is always a row in loan_emilists for each id in loans, if not you need to join, and then use not exists() :

SELECT DISTINCT s.id FROM loans s
INNER JOIN loan_emilists s1
 ON(s1.loan_id = s.id and s1.status = 1)
WHERE NOT EXISTS(select 1 from loan_emilists t
                 where s.id = t.loan_id 
                   and t.status = 0)

It can also be done with a left join:

SELECT DISTINCT s.id FROM loans s
INNER JOIN loan_emilists s1
 ON(s1.loan_id = s.id and s1.status = 1)
LEFT JOIN loan_emilists t
 ON(t.loan_id = s.id AND t.status = 0)
WHERE t.loan_id is null

Upvotes: 4

Related Questions