CODEWITHSUNDEEP
sqlsql-servert-sqlleft-join
user1695981
user1695981

Reputation: 89

TSQL Left Join: Show results regardless of match

I have 3 tables, dbo.employees, dbo.cars, dbo.hours. I retrieve the three tables based on one another. dbo.mployees always has a row to be shown. dbo.cars sometimes does depending on employees, and dbo.hours sometimes does depending on cars.

SELECT          e.*, c.*, h.*
FROM            dbo.emploees e
LEFT JOIN       dbo.cars c ON e.id=c.employeeID
LEFT JOIN       dbo.hours h ON c.id=h.carID
WHERE           c.name='Honda City'
ORDER BY        e.id ASC;

I want to show all employees regardless. if there is a match on cars, show the car values, otherwise null values. if there is a match on huors, show them, otherwise null values.

The statement I have works great if there are matches on all three tables, when there aren't it displays nothing. No employees at all.

Upvotes: 0

Views: 1657

Answers (3)

Andrew Bickerton
Andrew Bickerton

Reputation: 478

If you want to show all employees who have either no car or a Honda City you will need to have the criteria in the WHERE clause:

SELECT          e.*, c.*, h.*
FROM            dbo.emploees e
LEFT JOIN       dbo.cars c ON e.id=c.employeeID
LEFT JOIN       dbo.hours h ON c.id=h.carID
WHERE           c.name='Honda City' OR c.ID IS NULL
ORDER BY        e.id ASC;

If you want to show all employees (regardless of car type), and only show the car details and hours when the car is a Honda City, you should have the criteria in the JOIN clause:

SELECT          e.*, c.*, h.*
FROM            dbo.emploees e
LEFT JOIN       dbo.cars c ON e.id=c.employeeID AND c.name='Honda City'
LEFT JOIN       dbo.hours h ON c.id=h.carID
ORDER BY        e.id ASC;

Upvotes: 0

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

There must be some other problems with your data cause your query is working perfectly fine. Please check here demo

SQL Demo

If you want to filter records for left join then you need to add condition on join clause like below. If you add filter condition in where clause then it will filter whole result, so it will not returns other employees whose car name is null. Condition in where clause is ok for inner join, but not for left join.

SELECT          e.*, c.*, h.*
FROM            dbo.employees e
LEFT JOIN       dbo.cars c ON e.id=c.employeeID AND c.name='Honda City'
LEFT JOIN       dbo.hours h ON c.id=h.carID
ORDER BY        e.id ASC;

Upvotes: 3

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

SELECT e.*, ch.*
FROM  dbo.employees e
LEFT JOIN       
(
Select c.*, h.*
From dbo.cars c
LEFT JOIN dbo.hours h ON c.id=h.carID
WHERE c.name='Honda City'
) ch 
ON e.id = ch.employeeID

ORDER BY e.id ASC;

As soon as you added the where clause all the employees with no car named Honda city got filtered out.

Upvotes: 0

Related Questions