sqlfull-outer-join

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


A

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

B

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]