I have a table that links users. Consider the following:
**Table contracts:** contract_id int, contract_number varchar, user_id int **Table users:** user_id int **Table user_links** user_id int, linked_user_id int
The user_links table can have 0 rows for a particular user_id, given the user doesn’t have linked users, so a select statement can return either a row or NULL.
The approach with
left join user_links ul on ul.user_id = contracts.user_id OR ul.linked_user_id = contracts.user_id doesn’t seem to work if there is no row in the user_links table.
Given only an int user_id, how can I get rows from the contracts table for both user_id AND linked_user_id? For example, if the user_id 1 has a linked_user_id 2, I need the rows from contracts for both users; however, if the user doesn’t have a row in user_links table, I still need to get their contracts.
Advertisement
Answer
Assuming your input user_id is the variable @user_id, then the below query will get you all the contracts of that user, and if any linked user.
SELECT * from contracts c
where c.user_id = @user_id
OR c.user_id IN ( SELECT linked_user_id from user_links ul
WHERE ul.user_id = @user_id)