Menu
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.
954
You join two tables by creating a relationship in the WHERE clause between at least one column from one table and at least one column from another. The join creates a temporary composite table where each pair of rows (one from each table) that satisfies the join condition is linked to form a single row.
The join is done by the JOIN operator. In the FROM clause, the name of the first table ( product ) is followed by a JOIN keyword then by the name of the second table ( category ). This is then followed by the keyword ON and by the condition for joining the rows from the different tables.
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
