flatcoke flatcoke - 2 months ago 7
SQL Question

How to join according to a field

I have user table in my database and there is a user_type field, which has 0 or 1. if the value is 0, I want it to be joined with teacher table if not with student table.

is it possible?

Answer

You could also use cross apply or a CTE but as you dont specify the DB your using this should work as a generic solution

Select t1.fields,t2.fields
From user_table t1
LEFT JOIN teacher_table t2 
ON t1.ID=t2.ID 
and t1.user_type_field=0
UNION ALL
Select t1.fields,t2.fields
From user_table t1
LEFT JOIN student_table t2 
ON t1.ID=t2.ID 
and t1.user_type_field=1