mySQL: How to select FROM table WHERE IN LIST and NOT IN another list
Is it possible to select values from a table, where they don't exist in one list, but do exist in another... or they other way around?
E.g.
SELECT COUNT(g.`property`) as `number`, g.`property`
FROM `foo` g
WHERE `theID` IS IN (开发者_运维技巧SELECT `theID`
FROM `tableofIDS`
WHERE `theID` = '54252')
AND NOT IN (SELECT `theID`
FROM `anotherTableofIDS`
WHERE `theID` = '54252')
SELECT COUNT(g.`property`) as `number`, g.`property`
FROM `foo` g
WHERE `theID` IN (SELECT `theID` FROM `tableofIDS` WHERE `theID` = '54252')
AND `theID` NOT IN (SELECT `theID` FROM `anotherTableofIDS` WHERE `theID` = '54252')
GROUP BY g.`property`
Alternativly, you can use joins which will perform better:
SELECT COUNT(g.`property`) as `number`, g.`property`
FROM `foo` g JOIN (
SELECT `theID`
FROM `tableofIDS`
WHERE `theID` = '54252'
) id1 ON g.theID = id1.theID
LEFT JOIN (
SELECT `theID`
FROM `anotherTableofIDS`
WHERE `theID` = '54252'
) id2 ON g.theID = id2.theID
WHERE id2.theID IS NULL
GROUP BY g.`property`
Didn't think it through but discovered 2 syntax errors. Try
SELECT COUNT(g.`property`) as `number`, g.`property` FROM `foo` g WHERE `theID` IN (SELECT `theID` FROM `tableofIDS` WHERE `theID` = '54252') AND `theID` NOT EXISTS (SELECT `theID` FROM `anotherTableofIDS` WHERE `theID` = '54252')
Edit: Changed NOT IN
to NOT EXISTS
I think maybe what you're looking for is just this:
SELECT COUNT(g.property) as `number`, g.property FROM foo g JOIN tableofIDs i USING (theID) RIGHT JOIN anothertableofIDs x USING (theID) WHERE g.theID = '54252' AND x.theID IS NULL GROUP BY g.property
精彩评论