This will work: select distinct X from a as a where Y in(25) and not exists (select X from a as b where a.X = b.X and b.Y in(24))
----- Original Message ----- From: "Tim Molter" <tim.mol...@gmail.com>
To: <mysql@lists.mysql.com> Sent: Sunday, December 27, 2009 4:04 PM Subject: Is there a better way than this?
I'm new to MySQL and I'm looking for some guidance. I have a table A, with two columns X and Y with the following data: | X | Y | 1 24 1 25 2 25 2 26 3 27 I want my SQL query to return "2" following this verbose logic: SELECT DISTINCT X FROM A WHERE Y equals 25 and Y also does NOT equal 24. I came up with the following SQL, which gives me my desired result, but is there a better way to do it? Can it be achieved using MINUS or UNION somehow? BTW, I'm using IN here because I intend to replace the single numbers (24 and 25) with arrays that have 0 to N members. SELECT DISTINCT X FROM `A` WHERE X IN ( SELECT X FROM `A` WHERE Y IN (25) ) AND X NOT IN ( SELECT X FROM `A` WHERE Y IN (24) ) Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=my...@dawiz.net
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org