SQL: SELECT Statement to Return All Records, JOIN Value Is NULL, Query Returns No Records

Question

I have a SQL Statement I am executing to return data, the statement joins several other tables, so for the records returned, I can display the Name of the field, rather than the ID.

Here is part of the SQL:

SELECT 
    HardwareAsset.HardwareAssetTitle,
    HardwareAsset.HardwareAssetAssetTag,
    Department.DepartmentTitle AS HardwareAssetDepartmentTitle,
    CostCentre.DepartmentTitle AS HardwareAssetCostCentreTitle,
FROM 
    HardwareAsset
INNER JOIN 
    Department Department ON (Department.DepartmentID = HardwareAsset.HardwareAssetDepartmentID)
INNER JOIN 
    Department CostCentre ON (CostCentre.DepartmentID = HardwareAsset.HardwareAssetCostCentreID)

My issue is that, even though the query executes successfully, because certain columns (i.e the ones mentioned above) have a value of NULL, the query seems to return no records, even though there are records within the table.

I have tried executing when records have the columns filled in and records show. Any ideas?

Try LEFT JOIN instead of INNER JOIN, and it should return records even when there is no match in the second table. — beercodebeer
– beercodebeer, Commented Nov 10, 2016 at 21:48

S3S · Accepted Answer · 2016-11-10 21:54:42Z

4

When you use INNER JOIN the join field, like DepartmentID, MUST have a valid record in both tables. If you want to return records from your main table regardless if they are in your join tables you have to use LEFT JOIN.

Change INNER JOIN to LEFT JOIN.

edited Nov 10, 2016 at 21:54

S3S

25.2k5 gold badges29 silver badges48 bronze badges

answered Nov 10, 2016 at 21:51

Takehana

744 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheTechnicalPaladin Over a year ago

Cheers Guys! much appreciated, as you can probably tell, I am pretty new to SQL.

Collectives™ on Stack Overflow

SQL: SELECT Statement to Return All Records, JOIN Value Is NULL, Query Returns No Records

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related