Postgresql - Join two tables on multiple columns with case that return not fully matched raws with replacement of not matched value

NyashMyash
May 6, 2023
363 views
2 votes
3 Answers

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?

Tags: postgresql sql

Answers

if codt codt is an emtypoyt string = works, you neeed only take care of the NULL if no match is found

CREATE TABLE A (
  "raw_number" INTEGER,
  "ars" INTEGER,
  "bill" INTEGER,
  "codt" VARCHAR(7),
  "c4" INTEGER
);

INSERT INTO A
  ("raw_number", "ars", "bill", "codt", "c4")
VALUES
  ('1', '900', '603121','', '123'),
  ('2', '900', '603121', 'YPR-003', '234'),
  ('3', '900', '603121', 'Psp-123', '345'),
  ('4', '900', '603121', '', '456'),
  ('5', '900', '603121', '', '567');

CREATE TABLE

INSERT 0 5

CREATE TABLE B (
  "raw_number" INTEGER,
  "ars" INTEGER,
  "bill" INTEGER,
  "codt" VARCHAR(7),
  "s4" VARCHAR(3)
);

INSERT INTO B
  ("raw_number", "ars", "bill", "codt", "s4")
VALUES
  ('1', '900', '603121', 'Psp-123', 'asd'),
  ('2', '900', '603121', '', 'zxc'),
  ('3', '900', '455000', '', 'F');

CREATE TABLE

INSERT 0 3

SELECT a.ars, a.bill, a.codt, a.c4, COALESCE(b.s4,(SELECT s4 FROM B b1 WHERE a.ars = b1.ars AND a.bill = b1.bill and codt = ''))
FROM A a
LEFT JOIN B b ON
a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt

ars	bill	codt	c4	coalesce
900	603121		123	zxc
900	603121		456	zxc
900	603121		567	zxc
900	603121	Psp-123	345	asd
900	603121	YPR-003	234	zxc

SELECT 5

fiddle
t

- SelVazi
- May 6, 2023 at 1:21 pm
- 0 votes
0
This query will return the data you requested but s4 is missing.
```
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 and a.codt <> ''
  order by ars, bill, c4
```
This will return :

ars bill codt c4 s4

900 603121 null 123 null

900 603121 null 234 null

900 603121 Psp-123 345 asd

900 603121 null 456 null

900 603121 null 567 null

Then will get s4 from table B when s4 is null using inner join :
```
select DISTINCT s.ars, s.bill, s.codt, s.c4, case when s.s4 is not null then s.s4 else b.s4 end as s4 
from B b
inner join (
  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 and a.codt <> ''
) as s on s.ars = b.ars and s.bill = b.bill
where b.codt = ''
order by ars, bill, c4
```
Result :

ars bill codt c4 s4

900 603121 null 123 zxc

900 603121 null 234 zxc

900 603121 Psp-123 345 asd

900 603121 null 456 zxc

900 603121 null 567 zxc

Demo here
Login or Signup to reply.

- ahmed
- May 6, 2023 at 1:36 pm
- 0 votes
0
As I understood, you want to match the codt value that exists in A but not in B with the empty string codt from B, you could check the existence of codt in B before joining as the following:
```
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM 
  (
    select ars, bill, 
      case 
        when 
         exists(select 1 from B d where d.ars=t.ars and d.bill=t.bill and d.codt= t.codt) then codt 
        else '' 
      end as codt, c4
   from A t
  ) a
LEFT JOIN B b ON
a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt
ORDER BY c4
```
See demo
Login or Signup to reply.