Gavin, very nice,
- michael dykman On Mon, Dec 28, 2009 at 2:16 PM, Gavin Towey <gto...@ffn.com> wrote: > 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=mdyk...@gmail.com > > -- - michael dykman - mdyk...@gmail.com "May you live every day of your life." Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
