Skip to content
Advertisement

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

I have a table that links users. Consider the following:

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.

Advertisement