CODEWITHSUNDEEP
mysqlmaster-detail
Amir Surnay
Amir Surnay

Reputation: 404

how to filter master detail tables by NOT IN?

I have two tables like these

tblMaster
---------
id  title
---------
1    a
2    b
3    c
4    d

tblDetail
---------
dId  mId
---------
100  1
100  2
100  4
101  1
101  3
102  2
102  3

i join them and i use this query to find items from tblMaster that dont have detail item id (1,4)

SELECT m.id, m.title FROM tblMaster m
INNER JOINT tblDetail d on d.mId=m.id
WHERE m.id NOT IN (SELECT d2.mId FROM tblDetail d2 WHERE d2.dId IN (1,4))
GROUP BY m.id, m.title

I have lots of tables and i want to filter them like what i mentioned. is there an easier way or a general way to do all?

also... performance is important too! it is Ok if there is a solution in Yii Activerecord.

Upvotes: 0

Views: 74

Answers (1)

jmarkmurphy
jmarkmurphy

Reputation: 11493

Use an outer join join:

SELECT m.id, m.title FROM tblMaster m
LEFT OUTER JOIN tblDetail d on d.mId=m.id
WHERE d.dId is null

Upvotes: 1

Related Questions