Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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'
share|improve this question
up vote 9 down vote accepted

You must move the test on beenverified from the WHERE clause to LEFT ... ON ...:

SELECT sa.empname
    , coalesce(Da.itemssold, 0)
FROM #ShowAll sa
LEFT JOIN #Data da
    ON sa.empid = da.empid 
    AND da.beenverified = 'Yes';

With a LEFT JOIN, when there is no match on empid between the two tables, this query (minus the WHERE clause) returns NULL for beenverified (and other columns from #Data):

empname itemssold   beenverified
JJ      14          Yes
AA      12          No
BB      13          No
CC      NULL        NULL
DD      NULL        NULL
RR      NULL        NULL

If beenverified is tested in the WHERE 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 the WHERE clause, this query behaves like an INNER JOIN ....

Coalesce is then added in order to replace NULL by 0.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.