How can I SELECT where there is a specific link in a linking table or there is no link?

Question

I have two tables:

Videos
--------------
VideoID
VideoGroupID
CreatorUserID

and

VideoTags
--------------
VideoID
TagID

I want to select all of the VideoGroupIDs where the CreatorID is 1, 2, or 3 and there is either tag 10, 11, or 12 associated with the video OR there are no tags associated with the video. Can I do this in a single query?

Answer 1

This will get everything from the "Left" table which is Videos - try and match it up to the VideoTags table based on the VideoId. Then it will restrict the results to those where the tag is in (10, 11, 12) or if there were no TagId's

select *
from Videos V
left join VideoTags VT
on V.VideoId = VT.VideoId
where V.CreatorUserId in (1,2,3)
and (VT.TagId in (10, 11, 12) OR TagId is null)

Original answer - where the description matched the query - but it didn't solve the question

This will get everything from the "left" table - which is videos - where the CreatorId is in (1,2,3) and match it up to any VideoTags where the TagId is in (10,11,12) if it can't match to a VideoTag it'll return null for the VideoTag columns

select *
from Videos V
left join VideoTags VT
on V.VideoId = VT.VideoId
and V.CreatorUserId in (1,2,3)
and VT.TagId in (10, 11, 12)

Answer 2

I think you want a left join with a null check

SELECT DISTINCT v.videogroupid
FROM videos v 
    LEFT JOIN videotags vt on v.videoid = vt.videoid
WHERE
    v.creatoruserid in (1, 2, 3) 
    and (
        vt.tagid is null 
        or vt.tagid in (10, 11, 12)
    )