From: Chris W
Date: December 28 2009 2:01am
Subject: Re: Is there a better way than this?
List-Archive: http://lists.mysql.com/mysql/219939
Message-Id: <4B38117F.1080304@cox.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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!
>
>