List:General Discussion« Previous MessageNext Message »
From:DaWiz Date:December 28 2009 10:00pm
Subject:Re: Is there a better way than this?
View as plain text  
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.molter@stripped>
To: <mysql@stripped>
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=1
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