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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
