From: Michael Dykman Date: December 28 2009 7:32pm Subject: Re: Is there a better way than this? List-Archive: http://lists.mysql.com/mysql/219954 Message-Id: <814b9a820912281132u2a6b45fdvd7bf7f5fd75cfab9@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Gavin, very nice, - michael dykman On Mon, Dec 28, 2009 at 2:16 PM, Gavin Towey wrote: > No, that won't work, remember that the WHERE clause is applied to each ro= w individually -- y is 25, then it also cannot possibly be 24 at the same t= ime, so AND condition has no meaning there. =A0What 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 a= ll x with the same value: > > SELECT x FROM a GROUP BY x HAVING sum(y=3D25) and not sum(y=3D24); > > Regards, > Gavin Towey > > -----Original Message----- > From: Chris W [mailto:4rfvgy7@stripped] > Sent: Sunday, December 27, 2009 6:02 PM > To: Tim Molter > Cc: mysql@stripped > 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: >> >> | =A0 X =A0 =A0| =A0 =A0Y =A0 =A0| >> =A0 =A0 1 =A0 =A0 =A0 =A0 =A024 >> =A0 =A0 1 =A0 =A0 =A0 =A0 =A025 >> =A0 =A0 2 =A0 =A0 =A0 =A0 =A025 >> =A0 =A0 2 =A0 =A0 =A0 =A0 =A026 >> =A0 =A0 3 =A0 =A0 =A0 =A0 =A027 >> >> 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dgtowey@stripped= m > > > This message contains confidential information and is intended only for t= he individual named. =A0If you are not the named addressee, you are notifie= d that reviewing, disseminating, disclosing, copying or distributing this e= -mail is strictly prohibited. =A0Please notify the sender immediately by e-= mail if you have received this e-mail by mistake and delete this e-mail fro= m your system. E-mail transmission cannot be guaranteed to be secure or err= or-free as information could be intercepted, corrupted, lost, destroyed, ar= rive late or incomplete, or contain viruses. The sender therefore does not = accept liability for any loss or damage caused by viruses or errors or omis= sions in the contents of this message, which arise as a result of e-mail tr= ansmission. [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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail= .com > > --=20 - michael dykman - mdykman@stripped "May you live every day of your life." Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon.