List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:December 28 2009 7:16pm
Subject:RE: Is there a better way than this?
View as plain text  
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: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:
>
> |   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=1


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
Thread
Is there a better way than this?Tim Molter28 Dec
  • Re: Is there a better way than this?John List28 Dec
  • Re: Is there a better way than this?Chris W28 Dec
    • Re: Is there a better way than this?Tim Molter28 Dec
    • RE: Is there a better way than this?Gavin Towey28 Dec
      • Re: Is there a better way than this?Michael Dykman28 Dec
  • Re: Is there a better way than this?DaWiz28 Dec