View showing incorrect data

Question

I have a very simple database. It contains 3 tables. The first is the primary input table where values go in. The 2nd and 3rd are there purely for translating values to names for a view. When I view the rows in table 1, I can see that column businessUnit contains valid values in all rows. When I add the Business_Units table (The 3rd table in this DB), all but 2 rows go away and despite the businessUnit value both being 1 in the 1st table, the view gives them different names.

I created a DB diagram and uploaded a screenshot to imgur. Link: https://i.sstatic.net/imSkq.jpg

I only have 2 relationships in the table. One from equipType on New_Equipment_User to id in Equipment_Type and one from businessUnit in New_Equipment_User to id in Business_Units. The weird thing is that the Equipment Type works perfectly, yet when I replicate the table, relationship and view information exactly, it doesn't work. Instead of 6 rows appearing, there are only 2 which share the same value in businessUnit, but gives 2 different names for it.

In case it matters, here is my view Query:

SELECT dbo.New_Equipment_User.id, dbo.Equipment_Type.name AS equipType, dbo.New_Equipment_User.jobNumber,     dbo.New_Equipment_User.costCode, 
  dbo.New_Equipment_User.reason, dbo.New_Equipment_User.mobile, dbo.New_Equipment_User.mobileQty, dbo.New_Equipment_User.mobileComment, 
  dbo.New_Equipment_User.laptop, dbo.New_Equipment_User.laptopQty, dbo.New_Equipment_User.laptopComment, dbo.New_Equipment_User.desktop, 
  dbo.New_Equipment_User.desktopQty, dbo.New_Equipment_User.desktopComment, dbo.New_Equipment_User.modem, dbo.New_Equipment_User.modemQty, 
  dbo.New_Equipment_User.modemComment, dbo.New_Equipment_User.printer, dbo.New_Equipment_User.printerQty, dbo.New_Equipment_User.printerComment, 
  dbo.New_Equipment_User.camera, dbo.New_Equipment_User.cameraQty, dbo.New_Equipment_User.cameraComment, dbo.New_Equipment_User.dateRequired, 
  dbo.New_Equipment_User.requestedBy, dbo.New_Equipment_User.dateRequested, dbo.New_Equipment_User.approvalStatus, 
  dbo.Business_Units.name AS businessUnit
FROM dbo.New_Equipment_User
     JOIN dbo.Equipment_Type ON dbo.New_Equipment_User.equipType = dbo.Equipment_Type.id
     JOIN dbo.Business_Units ON dbo.New_Equipment_User.id = dbo.Business_Units.id
WHERE  (dbo.New_Equipment_User.approvalStatus = '0')

And here is an image of the view since it is easier to read: https://i.sstatic.net/GTACs.jpg

Is anyone able to assist with why this might be happening?

Answer 1

Try using a LEFT JOIN

SELECT ...

FROM dbo.New_Equipment_User
     JOIN dbo.Equipment_Type ON dbo.New_Equipment_User.equipType = dbo.Equipment_Type.id
     LEFT JOIN dbo.Business_Units ON dbo.New_Equipment_User.id = dbo.Business_Units.id

This will ensure that all dbo.New_Equipment_User and all dbo.Equipment_Type is present