Home > Back-end >  Join on two columns, if null then only join on one
Join on two columns, if null then only join on one

Time:04-20

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?

CodePudding user response:

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

CodePudding user response:

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.

CodePudding user response:

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.

  • Related