'Join on two columns, if null then only join on one

I have the following two tables:

customers:

customer_id department_id
aaaa 1234
bbbb 3456

status:

department_id customer_id status
1234 NULL silver
3456 bbbb gold
1234 bbbb gold

I want to join status on customers, but if if it returns NULL I want to give the customer the department default. My ideal Output for this would be the following:

customer_id department_id status
aaaa 1234 silver
bbbb 3456 gold

I have tried to do two left joins, but am getting a memory usage error. Is there another way to do this that is efficient?

sqlperformance


Solution 1:[1]

You can do:

select c.*, coalesce(s.status, d.status) as status
from customers c
left join status d on d.department_id = c.department_id 
                  and d.customer_id is null
left join status s on s.department_id = c.department_id 
                  and s.customer_id = c.customer_id

Solution 2:[2]

This might work:

SELECT *,
    (
        SELECT TOP 1 status
        FROM status s
        WHERE s.customer_id = c.customer_id
           OR (c.customer_id IS NULL AND s.department_id = c.department_id)
        ORDER BY CASE WHEN s.customer_id is NOT NULL THEN 0 ELSE 1 END
     ) as status
FROM customers c

The kicker is what kind of database you're using. If it's MySql you might want LIMIT 1 instead of TOP 1. For Oracle you'd look at the ROWNUM field.

Solution 3:[3]

Assuming that there is always a match at least on the department_id, you need an INNER join and FIRST_VALUE() window function will pick the proper status:

SELECT DISTINCT
       c.customer_id,
       c.department_id,
       FIRST_VALUE(s.status) OVER (
         PARTITION BY c.customer_id, c.department_id
         ORDER BY CASE 
                    WHEN s.customer_id = c.customer_id THEN 1 
                    WHEN s.customer_id IS NULL THEN 2 
                    ELSE 3 
                  END 
       ) status
FROM customers c INNER JOIN status s
ON s.department_id = c.department_id;

Depending on the database that you use the code may be simplified.

See the demo.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 The Impaler
Solution 2 Joel Coehoorn
Solution 3 forpas

Related Questions