Select Query Help

I'm struggling - with my limited knowledge of SQL - to write a query
that does what I want it to, and I was hoping that someone here may be
able to help out.

I have a table with two columns, user1 and user2, which store user
ID's. For example:

user1 | user2
-----------------------------
1 | 4
1 | 2
1 | 3
3 | 2
3 | 1
5 | 1

What I want to do is select all the rows for which, say, user2 is 1,
but where user1 is such that there isn't a reverse combination. So
above, the only row that should come back is 5/1 as for the row 3/1
there is also a row 1/3.

I know that explanation isn't perhaps the clearest, but hopefully you
get the idea.

Thanks in advance,
James Baker

On 2010-09-01 18:30, James Baker wrote:
> I'm struggling - with my limited knowledge of SQL - to write a query
> that does what I want it to, and I was hoping that someone here may be
> able to help out.
>
> I have a table with two columns, user1 and user2, which store user
> ID's. For example:
>
> user1 | user2
> -----------------------------
> 1 | 4
> 1 | 2
> 1 | 3
> 3 | 2
> 3 | 1
> 5 | 1
>
> What I want to do is select all the rows for which, say, user2 is 1,
> but where user1 is such that there isn't a reverse combination. So
> above, the only row that should come back is 5/1 as for the row 3/1
> there is also a row 1/3.
>
> I know that explanation isn't perhaps the clearest, but hopefully you
> get the idea.
>

select t1.user1, t1.user2
from t t1
where t1.user2 = 1
and not exists (
select 1 from t t2
where (t1.user1, t1.user2) = (t2.user2, t2.user1)
);

/Lennart