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?
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]