Conditional JOIN different tables

conditional-statements, join, sql, sql-server-2008

Solution

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)

Problem

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

Original source