开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜