List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:December 28 2009 7:32pm
Subject:Re: Is there a better way than this?
View as plain text  
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.
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