0

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?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Newstar
  • 53
  • 3
  • Please ask 1 specific researched non-duplicate question. Please either ask re 1 bad query/function with obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & a [mre]. Then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. [ask] [Help] Basic questions are faqs. – philipxy Apr 09 '23 at 02:03
  • Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Apr 09 '23 at 02:04

1 Answers1

0

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]
markalex
  • 8,623
  • 2
  • 7
  • 32