CODEWITHSUNDEEP
sqlsql-serverjoinsql-server-2012
Aki
Aki

Reputation: 626

SQL Join to include NULL values

I'm trying to join 3 tables into a select statement but count the occurences of one while still showing the record if no occurences happen.

My example can be seen in the quick sqlFiddle that I've put together. I've tried to use left joins but it doesn't produce the result I want.

http://sqlfiddle.com/#!6/e2840/8

This is the SQL Statement:

SELECT O.OptionID,O.OptionName, Count(A.OptionID) AS Total
FROM Options as O
    LEFT JOIN Answers AS A  ON O.OptionID = A.OptionID 
    LEFT JOIN Users as U ON A.UserId = U.UserID  
WHERE   A.QuestionID = 1
GROUP BY O.OptionID,O.OptionName

What I want it to return is all the rows from the options table and display a 0 in the total column if no answers were found for that option.

What the current fiddle returns:

1

What I would like it to return:

2

Upvotes: 0

Views: 150

Answers (5)

HaBo
HaBo

Reputation: 14327

same as @mjsqu but will avoid subset. This is more better way of joining with out subsets

SELECT O.OptionID,O.OptionName, Count(A.OptionID) AS Total
FROM Options as O
    LEFT JOIN Answers AS A  ON O.OptionID = A.OptionID and A.QuestionID = 1
    LEFT JOIN Users as U ON A.UserId = U.UserID  
GROUP BY O.OptionID,O.OptionName

Upvotes: 0

mjsqu
mjsqu

Reputation: 5452

Subselect answer:

SELECT O.OptionID,O.OptionName, Count(A.OptionID) AS Total
FROM Options as O
LEFT JOIN 
(
    SELECT OPTIONID,USERID 
    FROM Answers WHERE QuestionID = 1
) AS A  ON O.OptionID = A.OptionID 
LEFT JOIN Users as U ON A.UserId = U.UserID  
GROUP BY O.OptionID,O.OptionName

Upvotes: 2

Paul Maxwell
Paul Maxwell

Reputation: 35623

Th Users table is not referenced, and this matches the expected result using the data at your sqlfiddle.

SELECT O.OptionID,O.OptionName, Count(A.OptionID) AS Total
FROM Options as O
    LEFT JOIN Answers AS A  ON O.OptionID = A.OptionID
                            AND A.QuestionID = 1
GROUP BY O.OptionID,O.OptionName

By the way: You do not gain any advantage by using a subquery. The added join condition does exactly the same job.

But you will gain an advantage by avoiding a redundant join.

Upvotes: 0

SDK
SDK

Reputation: 1542

Try some thing like below...

       SELECT O.OptionID,O.OptionName, Count(A.OptionID) AS Total
       FROM Options as O
       LEFT JOIN Answers AS A  ON O.OptionID = A.OptionID and A.questionId=1
       LEFT JOIN Users as U ON A.UserId = U.UserID  
       GROUP BY O.OptionID,O.OptionName

Upvotes: 0

Manoj Pandey
Manoj Pandey

Reputation: 1397

You are using a WHERE clause with LEFT JOIN, which is making it act like an INNER JOIN.

Try this:

SELECT O.OptionID, O.OptionName, Count(A.OptionID) AS Total
FROM Options as O
    LEFT JOIN Answers AS A  ON O.OptionID = A.OptionID 
    and A.QuestionID = 1
    LEFT JOIN Users as U ON A.UserId = U.UserID  
GROUP BY O.OptionID,O.OptionName

Upvotes: 2

Related Questions