[SOLVED] Outer join by group (recreate from last post due to wrong information)

Outer join by group (recreate from last post due to wrong information)

porow	sorow	poid	soid	poreference	posource	poprice	sopriceconfirm
1		214446	215712	AAA	PO	50	1
1		215355	217902	BBB	PO	-60	0

porow	sorow	poid	soid	poreference	sosource	soprice	sopriceconfirm
	1	214446	215712	AAA	SO	-190	0
	1	215355	217902	BBB	SO	0	0

select 
    b.[porow], a.[sorow], b.poid, a.soid, 
    b.[poreference], b.[posource], a.[poprice], a.[sosource], 
    b.[soprice], a.[sopriceconfirm]
from 
    tableA a
full outer join 
    tableB b on a.[sorow] = b.[porow]

I want this result:

porow	sorow	poid	soid	poreference	posource	poprice	sosource	soprice	sopriceconfirm
1	1	214446	215712	AAA	PO	50	SO	-190	1
1	1	215355	217902	BBB	PO	-60	SO	0	0

However, I'm getting more rows.

When poreference, porow, sorow match in both tables I want to show soprice from tableB joined with table tableA.

If I remove poreference 'BBB' from one table then it works. But not both.

How can I achieve this?

Solution

You just need to join on equal fields.

Something like this:

select 
    b.[porow], a.[sorow], b.poid, a.soid, 
    b.[poreference], b.[posource], a.[poprice], a.[sosource], 
    b.[soprice], a.[sopriceconfirm]
from tableA a
full outer join tableB b
 on     a.[poid] = b.[poid]
    and a.[soid] = b.[soid]
    and a.[poreference] = b.[poreference]