MySQL Forums
Forum List  »  Newbie

Re: Query Help
Posted by: Björn Steinbrink
Date: March 29, 2006 07:33PM

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)
T B ON (A.scity = B.scity AND A.suppid != B.suppid);


Options: ReplyQuote

Written By
March 29, 2006 06:27PM
Re: Query Help
March 29, 2006 07:33PM
March 30, 2006 05:44AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.