CODEWITHSUNDEEP
sqljoincase
Satuyan
Satuyan

Reputation: 47

SQL: CASE in JOIN table

We have: 3 tables

Relation one-to-one — users-staff and users-clients

Sample data:

users_table, client_table

Question: How to select data from these 3 tables optionally? If users.role_id = 4 join from clients, else join from staff. Can i do like something like this?

I try: 

SELECT "users" JOIN CASE WHEN users.role_id IN (4) THEN 'clients' ELSE 'staff' ON "users"."role_id" IN (4) THEN 'clients' ELSE 'staff'.user_id = users.id

But have error

ERROR: syntax error at or near "JOIN" LINE 2: JOIN CASE WHEN users.role_id IN (4) THEN 'clients' ELSE 'sta...

Upvotes: 0

Views: 1898

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use two queries and then use UNION ALL to combine the result:

SELECT
    <columns>
FROM users AS u
INNER JOIN clients AS c
    ON c.user_id = u.id
WHERE u.role_id = 4

UNION ALL

SELECT
    <columns>
FROM users AS u
INNER JOIN staff AS s
    ON s.user_id = u.id
WHERE u.role_id <> 4

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Is this what you want?

SELECT u.*,
       (CASE WHEN users.role_id IN (4) THEN 'clients' ELSE 'staff' END) as role
FROM users u
WHERE u."role_id" IN (4);

The above works. But if your tables have other columns, you might want:

select u.*,
       (case when c.user_id is not null then 'client' else 'staff' end)
from users u left join
     clients c
     on u.role_id = 4 and c.user_id = u.id left join
     staff s
     on u.role_id <> 4 and s.user_id = u.id;

Upvotes: 1

Related Questions