Skip to content
Advertisement

Return in which or statement results are from SQL [closed]

I’m having the following SQL statement, to return worksheets that teachers have liked, and worksheets that teachers made by themselves.

SELECT worksheets.title, worksheets.worksheet_id
FROM worksheets
WHERE worksheets.worksheet_id 
IN (
    SELECT worksheet_id FROM likes WHERE teacher_id = 5
) OR worksheets.worksheet_id 
IN (
    SELECT worksheet_id FROM worksheets WHERE teacher_id = 5
)

It’s working fine, but now I also want to return if the worksheet was liked, or made by the teacher. How do I do that? I tried things with an if statement, but could not figure out a working solution.

Advertisement

Answer

How about a left join and conditional logic?

select w.title, w.worksheet_id,
    (w.teacher_id = 5)           as is_made,
    (l.worksheet_id is not null) as is_liked
from worksheets w
left join from likes l 
    on l.worksheet_id = w.worksheet_id and l.teacher_id = 5
where w.teacher_id = 5 or l.worksheet_id is not null

This puts the “made or like” information in two different columns (after all, a teacher could have made and liked a worksheek).

OR sometimes kills performance. union all might be more efficient:

select title, worksheet_id, max(is_made) as is_made, max(is_liked) as is_liked
from (
    select title, worksheet_id, 1 as is_made, 0 as is_liked 
    from worksheets 
    where teacher_id = 5
    union all
    select w.title, w.worksheet_id, 0, 1
    from worksheets w
    where exists (select 1 from likes l where j.teacher_id = 5 and l.worksheet_id = w.worksheet_id)
) t
group by title, worksheet_id

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement