Pfthb

I have a table called

trade with the following column (removed alot of redundant data)

trade_id,buyer_company_code, seller_company_code, legacy_trade_number.

I intent to query all trade where there one trade's buyer_company_code = another trade's seller_company_code and the trade's seller_company_code = another buyer buyer_company code

I tried with the query that result in the screenshot below

select * from trade tradetable1 inner join trade tradetable2
 on tradetable1.seller_company_code=tradetable2.buyer_company_code and tradetable1.buyer_company_code=tradetable2.seller_company_code
 and tradetable1.legacy_trade_number<tradetable2.legacy_trade_number

It currently gives me the following (legacy_trade_number from one trade) and (legacy_trade_numbe from another trade) with duplicates (due to cartesian product)

5548 5554

5548 5555

5548 5556

5549 5554

5549 5555

5549 5556

5550 5554

5550 5555

5550 5556

but i requires it to be as follows

5548 5554

5549 5555

5550 5556

Where the two column have no duplicate value. Any suggestion/hint will be helpful! thanks

if you don't want duplicated result for the column legacy_trade_number then instead of select * you should select only the columns you really need

select distinct tradetable1.legacy_trade_number from (
 select * from trade 
) tradetable1 
inner join ( 
select * 
from trade ) tradetable2 on tradetable1.seller_company_code=tradetable2.buyer_company_code 
 and tradetable1.buyer_company_code=tradetable2.seller_company_code
 and tradetable1.legacy_trade_number<tradetable2.legacy_trade_number

any way the result could be obtained simple using

select disticnt t1.legacy_trade_number 
from trade t1 
inner join trade t2 on on t1.seller_company_code=t2.buyer_company_code 
 and t1.buyer_company_code=t2.seller_company_code
 and t1.legacy_trade_number<t2.legacy_trade_number

The duplication in evaluated at row level so .. if you need others column you should choose which value you want and use aggreagtion function for reduce the unuseful values

eg: using an aggregation function as min() you could

select t1.legacy_trade_number, min(t1.col1), min(t2.coln)
from trade t1 
inner join trade t2 on on t1.seller_company_code=t2.buyer_company_code 
 and t1.buyer_company_code=t2.seller_company_code
 and t1.legacy_trade_number<t2.legacy_trade_number
 group by t1.legacy_trade_number