left join or right join depending on data

Question

I am in a situation in which i need to join 2 tables but depending on datat i need sometimes to consider a table as primary and the other as joined, and viceversa. I make a simple example to recreate the situation, imagine i have 2 tables (temp tables here) in which tasks for an employee are contained:

#JOB_TASKS contains the tasks that deal with job for the employees

#FAMILY_TASKS contains the tasks that deal with family for the employees

if for ID_EMPLOYEE = 1, #FAMILY_TASKS has no records, while #JOB_TASKS has some records, #JOB_TASKS should be the main table and #FAMILY_TASKS the joined one. While if for ID_EMPLOYEE = 2, #FAMILY_TASKS has some records, but#JOB_TASKS has no records, #JOB_TASKS should be the joined table and #FAMILY_TASKS the main on.

Somehow these are the queries i have:

Select * 
FROM 
#FAMILY_TASKS FT LEFT OUTER JOIN
#JOB_TASKS JT ON JT.ID_EMPLOYEE = FT.ID_EMPLOYEE

--same query "right join" written as "left join":
Select * 
FROM 
#JOB_TASKS JT LEFT OUTER JOIN
#FAMILY_TASKS FT ON FT.ID_EMPLOYEE = JT.ID_EMPLOYEE

I would like to have a single query that does automatically the correct join. This could be done with a cursor, but how to perform this with a select statement?

I hope i expressed myself.

Answer 1

Op hasn't updated their post, so this is a bit of a guess. I think this could be achieved with a FULL OUTER JOIN, using the following logic:

DECLARE @EmployeeID int = 1;

SELECT * 
FROM #FAMILY_TASKS FT
     FULL OUTER JOIN #JOB_TASKS JT ON JT.ID_EMPLOYEE = JT.ID_EMPLOYEE
WHERE @EmployeeID IN (FT.ID_EMPLOYEE,JT.EmployeeID);

Answer 2

You can do that with a FULL OUTER JOIN and some conditions.

In psuedocode, your conditions would do the following:

(ID_EMPLOYEE = 1 AND #JOBTASKS.Column IS NOT NULL)
OR 
(ID_EMPLOYEE = 2 AND #FAMILYTASKS.Column IS NOT NULL)