Conditionally fallback to different join condition if stricter condition not matched

Question

I have 2 tables j and c.

Both tables have columns ports and sec, and JOIN ON j.ports = c.ports and c.sec = j.sec.

For j.port = 'ABC', if there is no c.sec = j.sec for the same ports, then JOIN ON LEFT(c.sec, 6) = LEFT(j.sec, 6)

For other j.ports, I only want to join ON j.ports = c.ports and c.sec = j.sec

How can I do that?

Example Data

Table c

+------+------------+------------+
| Port |    sec     |   Other    |
+------+------------+------------+
| ABC  | abcdefghij |  ONE       |
| ABC  | klmnop     |  TWO       |
| LMN  | qwertyuiop |  THREE     |
| XYZ  | asdfghjkl  |  FOUR      |
+------+------------+------------+

Table j

+------+------------+
| Port |    sec     |
+------+------------+
| ABC  | abcdefxxxx |
| ABC  | klmnop     |
| LMN  | qwertyuiop |
| XYZ  | zxcvbnm    |
+------+------------+

EDITED: Desired Results

+------+------------+------------+
| Port |    sec     |  other     |
+------+------------+------------+
| ABC  | abcdefghij |  ONE       |  --> mactching on sec's 1st 6 characters 
| ABC  | klmnop     |  TWO       |  --> mactching on sec
| LMN  | qwertyuiop |  THREE     |  --> mactching on sec
+------+------------+------------+

How is this different to your other question (https://stackoverflow.com/questions/55837372/different-join-values-depending-on-the-value-of-another-column) — Dale K, Apr 24 '19 at 20:52
Please show us a complete query, sample data, current result data and expected result data. — Dale K, Apr 24 '19 at 20:52
https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Dale K, Apr 24 '19 at 20:53
It is different as the first one had static rules for the different join conditions. This one depends on the result of the join then falls back to a different condition if no rows matched. The existing answers there don't answer this. It is a different question. — Martin Smith, Apr 24 '19 at 20:54
What are you able to do? Give a [mcve]. See [ask] & the voting arrow mouseover texts. Dumps of specifications are not on-topic SO questions. — philipxy, Apr 25 '19 at 03:12
Thank you all. I have edited the original post with desired result. — faujong, Apr 25 '19 at 20:15

score 0 · Answer 1 · answered Apr 24 '19 at 21:37

This does conditional joining:

select t1.*, t2.*
from j t1 inner join c t2
on t2.ports = t1.ports and
  case 
    when exists (select 1 from c where sec = t1.sec) then t1.sec 
    else left(t1.sec, 6) 
  end =
  case 
    when exists (select 1 from c where sec = t1.sec) then t2.sec 
    else left(t2.sec, 6) 
  end

I question its efficiency but I think it does what you need.
See the demo.

score 0 · Answer 2 · answered Apr 24 '19 at 23:20

You can do two outer joins and then do isnull type of operation. In oracle nvl is isnull of sqlserver

with c as 
(
    select 'ABC' port, 'abcdefghij' sec from dual
    union all select 'ABC', 'klmnop' from dual 
    union all select 'LMN', 'qwertyuiop' from dual
    union all select 'XYZ', 'asdfghjkl' from dual
),
j as 
(
    select 'ABC' port, 'abcdefxxxx' sec from dual
    union all select 'ABC', 'klmnop' from dual 
    union all select 'LMN', 'qwertyuiop' from dual
    union all select 'XYZ', 'zxcvbnm' from dual
)
select c.port, c.sec, nvl(j_full.sec, j_part.sec) j_sec
from c
     left outer join j j_full on j_full.port = c.port and j_full.sec = c.sec
     left outer join j j_part on j_part.port = c.port and substr(j_part.sec,1,6) = substr(c.sec,1,6)
order by 1,2

score 0 · Answer 3 · answered Apr 25 '19 at 21:27

One way would be to just inner join on the less strict predicate then use a ranking function to discard unwanted rows in the event that c.port = 'ABC' and the stricter condition got a match for a particular c.port, c.sec combination.

with cte as
(
select c.port as cPort, 
       c.sec as cSec, 
       c.other as other,
       j.sec as jSec, 
       RANK() OVER (PARTITION BY c.port, c.sec ORDER BY CASE WHEN c.port = 'ABC' AND j.sec = c.sec THEN 0 ELSE 1 END) AS rnk
from c inner join  j on left(j.sec,6) = left(c.sec,6)
)
SELECT cPort, cSec, other, jSec
FROM cte 
WHERE rnk = 1

Conditionally fallback to different join condition if stricter condition not matched

3 Answers3

Linked