SQL not returning the proper values

JavaMan :

My tables are:

Equipment table 
EquipmentID (pk) 
Type 
Make 
Model 
Damaged
Description

EquipmentLoan table 
EmployeeID
EquipmentID (fk Equipment.EquipmentID) 
Start 
End 
Current

I am trying to return the equipment ID, make, model and description of equipment that

Is not marked as damaged
Not currently on loan
Have the word 'video' in its type or description

However, the code I currently have:

SELECT Equipment.EquipmentID,Equipment.Make,Equipment.Model,Equipment.Description 
FROM Equipment 
INNER JOIN EquipmentLoan ON Equipment.EquipmentID=EquipmentLoan.EquipmentID 
WHERE Equipment.Damaged = 0 
AND EquipmentLoan.Current = 0 
AND (Equipment.Type LIKE "%video%" OR Equipment.Description LIKE "%video%")

This currently returns no results, when there should be two results returned.

However when I use the query:

SELECT e.EquipmentID, e.Make, e.Model, e.Description 
FROM Equipment e, EquipmentLoan l 
WHERE e.Damaged = 0 
AND l.Current = 0 
AND (e.Type LIKE "%video%" or e.Description LIKE "%video%")

The correct results are returned, along with an extra result that doesn't match the conditions. I cannot figure out what the problem is.

Barmar :

Your query only returns equipment that has a matching row in EquipmentLoan and that row has Current = 0. If you also want equipment that has no matching row at all, you need to use a LEFT JOIN, not INNER JOIN. Then you check if this returns NULL for the lack of a match.

SELECT e.EquipmentID,e.Make,e.Model,e.Description 
FROM Equipment AS e
LEFT JOIN EquipmentLoan AS l ON e.EquipmentID=l.EquipmentID AND l.Current = 1
WHERE e.Damaged = 0 
AND (e.Type LIKE "%video%" OR e.Description LIKE "%video%")
AND l.EquipmentID IS NULL

Collected from the Internet

Please contact [email protected] to delete if infringement.