This is the result set that I get returned using my query:
empname itemssold
JJ 14
But this is my desired result set:
empname itemssold
AA 0
BB 0
CC 0
DD 0
JJ 14
RR 0
It seems to pulls all employees until I add in the where
clause to my query which makes me wonder if I am adding it in the wrong place, or am I setting up my query incorrectly?
Below is DDL & My query I use:
Create Table #ShowAll
(
empname varchar(1000)
,empid varchar(100)
)
Create Table #Data
(
empid varchar(100)
,itemssold int
,beenverified varchar(100)
)
Insert Into #ShowAll Values
('JJ', 'J1'),
('AA', 'A1'),
('BB', 'B1'),
('CC', 'C1'),
('DD', 'D1'),
('RR', 'R1')
Insert Into #Data Values
('J1','14', 'Yes'),
('A1', '12','No'),
('B1', '13', 'No')
Select sa.empname
, Da.itemssold
FROM #ShowAll sa
LEFT JOIN #Data da
ON sa.empid = da.empid
WHERE da.beenverified = 'Yes'
Best Answer
You must move the test on
beenverified
from theWHERE
clause toLEFT ... ON ...
:With a
LEFT JOIN
, when there is no match onempid
between the two tables, this query (minus theWHERE
clause) returnsNULL
forbeenverified
(and other columns from #Data):If
beenverified
is tested in theWHERE
clause, it does not return rows where the condition is not met:beenverified IS NULL
beenverified <> 'Yes'
(i.e.= 'No'
)Only row
JJ
is then valid. With the test in theWHERE
clause, this query behaves like anINNER JOIN ...
.Coalesce
is then added in order to replaceNULL
by0
.