CODEWITHSUNDEEP
sql-server
TheTechnicalPaladin
TheTechnicalPaladin

Reputation: 89

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

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?

Upvotes: 0

Views: 134

Answers (1)

Takehana
Takehana

Reputation: 74

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.

Upvotes: 4

Related Questions