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=gcdmg-mysql-2@m.gmane.org

Re: Is there a better way than this?

--------------060503080807020107030207
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 12/27/2009 06:04 PM, 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.
Since y=25 is associated with both x=1 and x=2, there's no way a simple
select can result in 2.

Perhaps your assignment calls for the count() of the results?

select count(*) from A where y = 25

Good luck,

John

--------------060503080807020107030207--

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=gcdmg-mysql-2@m.gmane.org

Re: Is there a better way than this?

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=3D1, y=3D25) since y=3D25 is
associated with both x=3D1 and x=3D2. 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-m n-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:
>>
>> | =A0 X =A0 =A0| =A0 =A0Y =A0 =A0|
>> =A0 =A01 =A0 =A0 =A0 =A0 =A024
>> =A0 =A01 =A0 =A0 =A0 =A0 =A025
>> =A0 =A02 =A0 =A0 =A0 =A0 =A025
>> =A0 =A02 =A0 =A0 =A0 =A0 =A026
>> =A0 =A03 =A0 =A0 =A0 =A0 =A027
>>
>> 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!
>>
>>
>



--=20
~Tim
http://obscuredclarity.blogspot.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Is there a better way than this?

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 tim=
e, 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=3D25) and not sum(y=3D24);

Regards,
Gavin Towey

-----Original Message-----
From: Chris W [mailto:4rfvgy7@cox.net]
Sent: Sunday, December 27, 2009 6:02 PM
To: Tim Molter
Cc: mysql@lists.mysql.com
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=3Dgtowey@ffn.com


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f 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-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate 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 transmis=
sion. [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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Is there a better way than this?

Gavin,

very nice,

- michael dykman

On Mon, Dec 28, 2009 at 2:16 PM, Gavin Towey wrote:
> No, that won't work, remember that the WHERE clause is applied to each ro=
w individually -- y is 25, then it also cannot possibly be 24 at the same t=
ime, so AND condition has no meaning there. =A0What 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 a=
ll x with the same value:
>
> SELECT x FROM a GROUP BY x HAVING sum(y=3D25) and not sum(y=3D24);
>
> Regards,
> Gavin Towey
>
> -----Original Message-----
> From: Chris W [mailto:4rfvgy7@cox.net]
> Sent: Sunday, December 27, 2009 6:02 PM
> To: Tim Molter
> Cc: mysql@lists.mysql.com
> 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:
>>
>> | =A0 X =A0 =A0| =A0 =A0Y =A0 =A0|
>> =A0 =A0 1 =A0 =A0 =A0 =A0 =A024
>> =A0 =A0 1 =A0 =A0 =A0 =A0 =A025
>> =A0 =A0 2 =A0 =A0 =A0 =A0 =A025
>> =A0 =A0 2 =A0 =A0 =A0 =A0 =A026
>> =A0 =A0 3 =A0 =A0 =A0 =A0 =A027
>>
>> 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.co=
m
>
>
> This message contains confidential information and is intended only for t=
he individual named. =A0If you are not the named addressee, you are notifie=
d that reviewing, disseminating, disclosing, copying or distributing this e=
-mail is strictly prohibited. =A0Please notify the sender immediately by e-=
mail if you have received this e-mail by mistake and delete this e-mail fro=
m your system. E-mail transmission cannot be guaranteed to be secure or err=
or-free as information could be intercepted, corrupted, lost, destroyed, ar=
rive late or incomplete, or contain viruses. The sender therefore does not =
accept liability for any loss or damage caused by viruses or errors or omis=
sions in the contents of this message, which arise as a result of e-mail tr=
ansmission. [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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Is there a better way than this?

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"
To:
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=mysql@dawiz.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org