CODEWITHSUNDEEP
mysqlsqlselect
Zerium
Zerium

Reputation: 17333

Select from one table on truthful IF condition, select from another on FALSE?

Here's what I've currently got:

SELECT *, CASE WHEN chat.user_type = 's'
  THEN (SELECT name FROM students WHERE students . id = chat . user_id) AS name
  ELSE (SELECT name FROM teachers WHERE teachers . id = chat . user_id) AS name
FROM chat WHERE classroom_id = '2' ORDER BY timestamp ASC

But it isn't working for me. I'm not sure how I would write this, so that it can select the name from one of two tables.

Upvotes: 0

Views: 20

Answers (1)

juergen d
juergen d

Reputation: 204884

SELECT *, CASE WHEN chat.user_type = 's'
               THEN students.name
               ELSE teachers.name 
          END AS name
FROM chat 
LEFT JOIN students ON students.id = chat.user_id
LEFT JOIN teachers ON teachers.id = chat.user_id
WHERE classroom_id = '2' 
ORDER BY timestamp ASC

or

SELECT *, coalesce(students.name, teachers.name) AS name
FROM chat 
LEFT JOIN students ON students.id = chat.user_id AND chat.user_type = 's'
LEFT JOIN teachers ON teachers.id = chat.user_id AND chat.user_type = 't'
WHERE classroom_id = '2' 
ORDER BY timestamp ASC

Also see this great explanation of joins

Upvotes: 5

Related Questions