I have table A
raw_number | ars | bill | codt | c4 |
---|---|---|---|---|
1 | 900 | 603121 | 123 | |
2 | 900 | 603121 | YPR-003 | 234 |
3 | 900 | 603121 | Psp-123 | 345 |
4 | 900 | 603121 | 456 | |
5 | 900 | 603121 | 567 |
and table B
raw_number | ars | bill | codt | s4 |
---|---|---|---|---|
1 | 900 | 603121 | Psp-123 | asd |
2 | 900 | 603121 | zxc | |
3 | 900 | 455000 | F |
Im joining table A with table B on "ars", "bill", "codt" columns.
My point is getting this result:
ars | bill | codt | c4 | s4 |
---|---|---|---|---|
900 | 603121 | 123 | zxc | |
900 | 603121 | 234 | zxc | |
900 | 603121 | Psp-123 | 345 | asd |
900 | 603121 | 456 | zxc | |
900 | 603121 | 567 | zxc |
columns "raw_number" added for explanations : for raw number 3 from table A match must be raw 1 from table B, cos its fully matched, but raw number 2 from A must match with raw 2 from B
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM A a
LEFT JOIN B b ON
a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt
is not working for me cos it returns null values from table B for raw number 3 A table
INNER JOIN is not a point too cos remove this raw
original query is more complicated because tables A and B is a results from another joins
UPDATE:
After an NBK answer i try this
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM A a LEFT JOIN B b ON a.ars = b.ars
AND a.bill = b.bill
AND a.codt IS DISTINCT FROM b.codt
UNION
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM A a LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill
AND a.codt = b.codt
it looks like i want but im not sure because my original query with this have another unions around and looks monstrous.. 1200+ raws
Maybe someone have another idea?
3
Answers
if codt codt is an emtypoyt string = works, you neeed only take care of the NULL if no match is found
fiddle
t
This query will return the data you requested but s4 is missing.
This will return :
Then will get s4 from table B when s4 is null using
inner join
:Result :
Demo here
As I understood, you want to match the
codt
value that exists in A but not in B with the empty stringcodt
from B, you could check the existence ofcodt
in B before joining as the following:See demo