SQL Join query returns null instead of names-CodePudding

I'm trying to join two tables to get the video title that is not borrowed in the copy table, and I think I've done everything correctly except the table values return null? Instead of the actual names it just says null, I'm new to SQL and don't really know what I'm doing wrong.

SELECT 
   video.vidTitle, 
   video.vidCertificationID, 
   copy.copCurrentlyHired 
FROM video 
RIGHT JOIN copy ON video.vidVideoID = copy.copCopyID 
WHERE copCurrentlyHired = 1;

some screnshot from phpMyAdmin

CodePudding user response：

SELECT 
   video.vidTitle, 
   video.vidCertificationID, 
   copy.copCurrentlyHired 
FROM video 
LEFT JOIN copy ON video.vidVideoID = copy.copCopyID 
  and copCurrentlyHired = 1; 
WHERE copy.copCopyID is null.

Why?

LEFT vs RIGHT: we want all records from video regardless so a left join from video to copy does this.
WHERE to AND: this ensures we only get copies that are currently hired when we join those that are not "currently hired" so the join will only return those currently hired.
WHERE: this ensures we get back only those records where we don't have a currently hired record in copy. But; this may not be what you're really after....

Edge case I'm worried about: if you have multiple records in copy for the same videoID and one is currentlyhired and the other is not. I'm not sure what you want to happen in this situation.

Maybe you're just after a left join where the currenctlyhired is <> 1 w/o a where...

So sample data with expected results helps us understand what you're trying to achieve.