SQL SELECT statement with a many-to-many extra table-CodePudding

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.

CodePudding user response：

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)