Join table on one of the row

oracleoracle-12c

I have a table, say, Person with fields

id  name
1    Jone
2    Jane

Got another table address

id   person_id   address_type
1    1           Permanent
2    1           Temporary

I want to select table Person to Address such that if a person has Temporary address then join it else join with Parmanent address.

I can do:

    select * 
    from person p 
    left join address at on at.person_id=p.id and at.type='Temporary' 
    left join address al on al.person_id=p.id and at.type='Permanent'

But joining address table twice seems like overkill here. Can I just join address only once such that I get:

-> If Temporary is there, join with Temporary
-> If temporary is not there, join with permanent
-> If pernamenet is not there join address with all null fields.

Help?

Best Answer

This could be done using a left join to a derived table that picks the correct address:

select p.*, a.id as address_id, a.address_type
from person p
  left join (
     select ad.*, 
            row_number() over (partition by ad.person_id 
                               order by case when ad.address_type = 'Temporary' then 1 else 2 end) as rn
     from address ad
  ) a on a.person_id = p.id and a.rn = 1;

The case expression for the order by inside the window function could also be written as decode(ad.address_type, 'Permanent', 1, 2), but I prefer standard SQL over Oracle specific one if both are equivalent.

Another option - that might or might not be faster - is to use a lateral join with a fetch first clause.

select p.*, a.id as address_id, a.address_type
from person p
  left join lateral (
     select *
     from address ad
     where ad.person_id = p.id
     order by case when ad.address_type = 'Temporary' then 1 else 2 end
     fetch first 1 rows only
  ) a on 1 = 1;

The lateral left join returns one row for each person_id from the outer query (because of the fetch first 1 rows only)

Related Solutions

Update with nested join problem

You can use NVL to return a default string when a null value is encountered:

SELECT NVL(fieldname,'Show this text instead of null')
FROM myTable;

In your case, I would try something like this:

SELECT CU.CUSTOMER#, INITCAP(CU.LASTNAME || ', ' || SUBSTR(CU.FIRSTNAME,1,1)) ||'.' AS "CUSTOMER NAME",
   INITCAP(NVL(RE.LASTNAME,'No Referral')) AS "REFERRED LAST NAME", 
   INITCAP(NVL(RE.FIRSTNAME,'No Referral')) AS "REFERRED FIRST NAME" 
FROM CUSTOMERS CU
LEFT OUTER JOIN CUSTOMERS RE ON CU.REFERRED = RE.CUSTOMER#;

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

That one caused me quite a bit of headache before I found a halfway elegant solution:

WITH x AS (
    SELECT BASE.id AS b_id
          ,BASE.start_date AS b_start_date
          ,BASE.end_date AS b_end_date
          ,CHILD.id AS c_id
          ,CHILD.start_date AS c_start_date
          ,CHILD.end_date AS c_end_date
          ,CASE WHEN DATE '2011-1-1' BETWEEN CHILD.start_date AND CHILD.end_date
                                     OR CHILD.id IS NULL THEN 0 ELSE 1 END AS c_hit
    FROM   BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
    WHERE  BASE.ID = 1
    AND    DATE '2011-1-1' BETWEEN BASE.start_date AND BASE.end_date
    )
SELECT b_id, b_start_date, b_end_date
      ,c_id, c_start_date, c_end_date
FROM   x
WHERE  x.c_hit = 0

UNION ALL
SELECT b_id, b_start_date, b_end_date
      ,NULL, NULL,         NULL
FROM   x
GROUP  BY b_id, b_start_date, b_end_date
HAVING min(c_hit) = 1;

Of course, the better solution would be to get rid of the restrictions and adjust the JOIN in the base query. As @a_horse expressed it so poetically in the comments above:

the perils of ORM frameworks

Anyway, to solve the question at hand:
After selecting rows where BASE.id and BASE.start_date / BASE.end_date match, we have four different cases:

No child at all, so CHILD.id is NULL.
Child matches.
Child does not match and no other matching child.
Child does not match but other matching child.

This is hard to solve on one query level. You could do it with window functions and CASE statements and DISTINCT, in fact I had a basic working query, but it gets monstrous.

Using a CTE dramatically simplifies the syntax.
In the CTE:

Filter BASE on id and date range. Still includes rows with non-matching children.
Assign aliases for identical column names
Tag rows where child matches the date

In the final SELECT:

Return all rows with matching children
Return one row where no matching children exist, with NULLs for child-columns.

Here is working demo on sqlfiddle.com for those who want to toy with it.

Best Answer

Related Solutions

Update with nested join problem

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

Related Question