Conditional JOIN different tables
Solution 1
You could use an outer join:
select *
from USER u
left outer join EMPLOYEE e ON u.user_id = e.user_id
left outer join STUDENT s ON u.user_id = s.user_id
where s.user_id is not null or e.user_id is not null
alternatively (if you're not interested in the data from the EMPLOYEE or STUDENT table)
select *
from USER u
where exists (select 1 from EMPLOYEE e where e.user_id = u.user_id)
or exists (select 1 from STUDENT s where s.user_id = u.user_id)
Solution 2
If you want to get all user data together You might have:
SELECT
user_id
,'Employee' AS Source
FROM
employee
UNION
SELECT
user_id
,'Student' AS Source
FROM
student
http://sqlfiddle.com/#!3/90216/22
Which can also be done with a full join and a CASE statement:
SELECT
ISNULL(e.user_id,s.user_id) AS user_id
,CASE WHEN e.user_id IS NULL THEN 'Student'
ELSE 'Employee'
END AS SOURCE
FROM
employee AS e
FULL JOIN student AS s
ON s.user_id = e.user_id
http://sqlfiddle.com/#!3/90216/29
the latter will combine people who are both students adn employees into one row and call them and employee. compare:
http://sqlfiddle.com/#!3/2aa3e/1 and http://sqlfiddle.com/#!3/2aa3e/2
where I have made user 1 a student and a employee
Solution 3
Such solution also can help you.
SELECT S.*, P.*
,CASE
WHEN S.ShipmentType = 'import' THEN SP.SupplierName
WHEN S.ShipmentType = 'export' THEN C.CustomerName
END AS ShipmentDesination
FROM tblShippments S
INNER JOIN tblProducts P ON S.productId = P.productID
LEFT OUTER JOIN tblCustomers C ON S.companyId = C.customerId AND S.ShipmentType = 'export'
LEFT OUTER JOIN tblSuppliers SP ON S.companyId = SP.supplierId AND S.ShipmentType = 'import'
Solution 4
If you look at employee and student tables as one, you can use left join:
select *
from user u
left join
(
select 'Employee' as UserType,
id,
user_id
from employee e
union all
select 'Student',
id,
user_id
from student s
) r
ON u.user_id = r.user_id
Related videos on Youtube
![juergen d](https://i.stack.imgur.com/LnDUq.gif?s=256&g=1)
juergen d
Updated on August 28, 2021Comments
-
juergen d almost 3 years
I want to know if a user has an entry in any of 2 related tables.
Tables
USER (user_id) EMPLOYEE (id, user_id) STUDENT (id, user_id)
A User may have an employee and/or student entry. How can I get that info in one query? I tried:
select * from [user] u inner join employee e on e.user_id = case when e.user_id is not NULL then u.user_id else null end inner join student s on s.user_id = case when s.user_id is not NULL then u.user_id else null end
But it will return only users with entries in both tables.
SQL Fiddle example
-
Martin Gal about 4 yearsPlease give more details about your solution. Posting just code is not a good way to answer a question.
-
REMITH over 3 yearsBest solution. UNION ALL will solve all conditional works... Great. Thank you.