skip to Main Content

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


  1. 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

    Login or Signup to reply.
  2. 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.
  3. 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.
Please signup or login to give your own answer.
Back To Top
Search