SQL Join query returns null instead of names

Question

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

Try to avoid using RIGHT JOIN, see: https://stackoverflow.com/questions/436345/when-or-why-would-you-use-a-right-outer-join-instead-of-left — Luuk, Nov 30 '21 at 14:24
In this case there are records in table `copy` with no matching records in table `video` — Luuk, Nov 30 '21 at 14:24
switch `right` to `left`, `where` to `and` and then add a new line `WHERE copy.copCopyID is null` though..... depending on data this may not be right either.... Need some sample data and expected results to know/better understand your siutaiton. — xQbert, Nov 30 '21 at 14:27
@xQbert Thank you so much i was working on this thing for four hours — poop, Nov 30 '21 at 14:31
Just ensure you're getting back the "Right" data. I'm not positive this handles all your cases correctly as it will depend on how those tables are used and what data is stored in them when. and ask questions if you don't understand "WHY" it works... that whole teach a person to fish thing... vs give a person a fish. — xQbert, Nov 30 '21 at 14:32
@poop you can remove the image from the question it contains anything useful. Add table description, some datas in text format and expected result — Ergest Basha, Nov 30 '21 at 15:06

score 0 · Accepted Answer · answered Nov 30 '21 at 14:36

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.

SQL Join query returns null instead of names

1 Answers1