Re: Query Help

[Björn Steinbrink]

This is my approach to the problem, I'll try to be a bit verbose about
how I constructed the query. There might be better approaches to this
problem though.
We need a join of the table with itself, the join condition is obviously
that the scity column is the same. Additionally, the suppid should differ
so we don't get a row like S1 | S1.
Then we're left with reversed rows, to solve this, we use LEAST and GREATEST
to have the smaller suppid always in the left column, and the bigger suppid
in the right column. Finally, we select only distinct rows.

select distinct
LEAST(A.suppid, B.suppid), GREATEST(A.suppid, B.suppid)
FROM
T A
JOIN
T B ON (A.scity = B.scity AND A.suppid != B.suppid);

HTH