No, that won't work, remember that the WHERE clause is applied to each row individually -- y is 25, then it also cannot possibly be 24 at the same time, so AND condition has no meaning there. What you're asking for there is the set of all x that have 25 as a y value, which is 1 and 2.
You need to use aggregates to create conditions that are meaningful for all x with the same value: SELECT x FROM a GROUP BY x HAVING sum(y=25) and not sum(y=24); Regards, Gavin Towey -----Original Message----- From: Chris W [mailto:4rfv...@cox.net] Sent: Sunday, December 27, 2009 6:02 PM To: Tim Molter Cc: mysql@lists.mysql.com Subject: Re: Is there a better way than this? Unless I am missing something, this should work. SELECT DISTINCT X FROM `A` WHERE Y IN (25) AND Y NOT IN (24) Chris W Tim Molter wrote: > 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=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
