List:General Discussion« Previous MessageNext Message »
From:Tim Molter Date:December 28 2009 9:21am
Subject:Re: Is there a better way than this?
View as plain text  
Thanks for the replies!

Chris, yeah, that's the first thing I tried. The problem though is
that SQL statement also returns Row #2 (x=1, y=25) since y=25 is
associated with both x=1 and x=2. I want it only to return row #3.

As John said, it may not be possible with a simple SQL statement.

My table is used as a mapping table for an M to N relationship similar
as described here:
http://stackoverflow.com/questions/1680855/sql-select-with-mn-relationship

My idea was to get a set of Xs with SELECT X FROM `A` WHERE Y IN (25)
and another set of Xs with SELECT X FROM `A` WHERE Y IN (24)

,then return the common elements between the two sets and use THAT set
to query X again.

Like I said, that approach works but I thought there might be a more
elegant way.





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



-- 
~Tim
http://obscuredclarity.blogspot.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