How to give precedence to join conditions in DB2 sql?-CodePudding

I have a join left join between table A and B on below given condition.

"Left" table A:

name	city	country
BOB	NA	NA
TOM	NEW YORK	USA

"Right" table B

name	flag	site
BOB	NA	NA
TOM	CITY	NEW YORK
TOM	COUNTRY	USA

Current result:

name	flag	site
BOB	NA	NA
TOM	CITY	NEW YORK
TOM	COUNTRY	USA

Expected result:

name	flag	site
BOB	NA	NA
TOM	CITY	NEW YORK

ON (B.FLAG=NA AND B.SITE = NA AND A.NAME=B.NAME)

OR (B.FLAG=CITY AND B.SITE=A.CITY AND A.NAME= B.NAME)

OR (B.FLAG=COUNTRY AND B SITE=A.COUNTRY AND A.NAME=B.NAME)

If not 1st ,then 2nd else 3rd condition

I want to give precedence to these three conditions that when 1st condition is satisfied, then the remaining two should not be considered.

If the second join condition is satisfied, then it should not join again as per second condition even if its possible.

Only one of the three join condition should used.

I am getting double rows because some rows satisfy both second and third condition which is not correct as per my requirement.

What should I add here? How can I achieve the desired precedence?

CodePudding user response：

Try this:

WITH 
  A (name, city, country) AS
  (
    VALUES
      ('BOB',   'NA',       'NA')
    , ('TOM',   'NEW YORK', 'USA')
  )
, B (name, flag, site) AS
  (
    VALUES
      ('BOB',   'NA',       'NA')
    , ('TOM',   'CITY',     'NEW YORK')
    , ('TOM',   'COUNTRY',  'USA')
  )
SELECT 
  A.NAME
, COALESCE (B1.FLAG, B2.FLAG, B3.FLAG)  AS FLAG  
, COALESCE (B1.SITE, B2.SITE, B3.SITE)  AS SITE  
FROM A
LEFT JOIN B B1 ON (B1.FLAG, B1.SITE, B1.NAME) = ('NA',      'NA',       A.NAME)
LEFT JOIN B B2 ON (B2.FLAG, B2.SITE, B2.NAME) = ('CITY',    A.CITY,     A.NAME) AND B1.NAME IS NULL
LEFT JOIN B B3 ON (B3.FLAG, B3.SITE, B3.NAME) = ('COUNTRY', A.COUNTRY,  A.NAME) AND B1.NAME IS NULL AND B2.NAME IS NULL

NAME	FLAG	SITE
BOB	NA	NA
TOM	CITY	NEW YORK

CodePudding user response：

Use brackets to separate different conditions. Otherwise, you may get wrong results. Try this.

( B.FLAG=NA AND B.SITE = NA AND A.NAME=B.NAME )
OR
(  B.FLAG=CITY AND B.SITE=A.CITY AND A.NAME= B.NAME )
OR 
( B.FLAG=COUNTRY AND B SITE=A.COUNTRY AND A.NAME=B.NAME )