I am not sure if this is supposed to use a subquery, or multiple temp tables...but any assistance would be great! i am trying to do the following in one query pull.

I want to pull, say, all rows where a User has Color=blue and Color=red. I am interested in pulling these multiple rows to determine which users CHANGED their Color from blue to red, or from red to blue.

The general query i have now is this. What is wrong and how can i improve it? thank you!

1. Does this return Zero results because I am asking that the row's value has BOTH blue and red at the same time? (which is impossible)
2. my other worry, is that if I use OR instead of AND, that i will include rows for users that are color blue, or color red, but did NOT change between the two colors.

SELECT *
FROM Table a
WHERE a.color='blue'
AND a.color='red'

I'm assuming userId as the id which defines user, you can replace with whatever you have in the table. The problem is called Relational Division. Hope this helps!

    select * from table where color in ('blue','red') and userId in(select a.userId
    from Table a
    where a.color in ('blue','red')
    group by a.userid  //whatever defines user uniquely
    having count(distinct a.color)=2)

more