join table on condition-CodePudding

I have 3 tables user, student_data, teacher_data. A user can be either student or a teacher. If it is the teacher I want to join user and teacher_data. And if it is a student then I want to join user with student_data.

How I can do this join with the condition.

CodePudding user response：

I'd combine the two data tables in a sub-query, and then join the users to that.

SELECT
  *
FROM
  usr   u
LEFT JOIN
(
  SELECT user_id, datum, xxx, NULL AS yyy FROM student_data
  UNION ALL
  SELECT user_id, datum, NULL,        yyy FROM teacher_data
)
  d
    ON d.user_id = u.id

https://dbfiddle.uk/?rdbms=oracle_21&fiddle=9b801ea739d42fe50c00ef4e17eaf143

NOTES:

The columns selected from the two data tables must match
Any unmatched columns must either be skipped or filled with NULL
Please don't call a table user, it's a reserved keyword and Oracle won't allow it.

CodePudding user response：

You can write it like this:

select   u.user_id,
         s.student_id,
         t.teacher_id
from usr u
left join student_data s  on u.user_id=s.student_id
left join teacher_data t  on u.user_id=t.teacher_id
where s.student_id is not null or t.teacher_id is not null
order by u.user_id

For every user_id check if he is a student or teacher, if he is student get his student column values else null, if he is a teacher get his teacher column values else null.

CodePudding user response：

maybe try a union - something like this

select user_id, user_other_stuff
from user, student_data
where user.user_id = student_data.user_id
UNION
select user_id, user_other_stuff
from user, teacher_data
where user.user_id = teacher_data.user_id