Gavin,
very nice,
- michael dykman
On Mon, Dec 28, 2009 at 2:16 PM, Gavin Towey <gtowey@stripped> wrote:
> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
--
- 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.
