Left Join not getting the resultset properly

Question

I have a table called supplier_rating. This table is used to store the rating and the no_of_stars received for each supplier. Rating can range from 1-5. A supplier can have an entry for all the ratings or they can some of the ratings. I want to create a SELECT statement that will return all the rating for supplier.

Code snippets

CREATE TABLE dbo.supplier_rating(
        supplier_id INT
        ,star       INT
        ,no_of_stars INT)

 INSERT INTO dbo.supplier_rating VALUES (100,5,50),(100,3,30),(200,2,20),(200,1,10);

I want a select statement that will return the resultset as the below for supplier_id = 100

supplier_id     rating  no_of_stars
100             5       50
100             4       0
100             3       30
100             2       0
100             1       0

Below is the query I am using. I am creating temp table that will have all the rating from 1-5 and I am doing LEFT JOIN with supplier_rating table , but it is returning the resultset I want

CREATE TABLE #temp_rating(star INT);
INSERT INTO #temp_rating VALUES (1),(2),(3),(4),(5);

SELECT r.star,sr.no_of_stars
FROM    #temp_rating r
LEFT JOIN dbo.supplier_rating sr ON r.star = sr.star
WHERE   sr.supplier_id = 100;

Quite similar to [this one](https://stackoverflow.com/a/59039388/4003419)? — LukStorms, Nov 26 '19 at 22:30
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Nov 27 '19 at 06:16

score 3 · Accepted Answer · answered Nov 26 '19 at 22:25

LEFT JOIN dbo.supplier_rating sr ON r.star = sr.star WHERE sr.supplier_id = 100

If you put a condition on the left joined table in the where clause, it becomes mandatory, and cannot be satisfied by records for which the left join did not succeed. This actually turns your left join to an inner join.

You need to move that condition from the where clause to the on clause of the join:

SELECT r.star,sr.no_of_stars 
FROM #temp_rating r 
LEFT JOIN dbo.supplier_rating sr ON r.star = sr.star AND sr.supplier_id = 100;

Left Join not getting the resultset properly

1 Answers1