Need help with JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with JOIN

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-31 : 14:16:03

Here's my schema:

---------------------------------
tblMembers
---------------------------------
MemberID | ChoiceID
---------------------------------

---------------------------------
tblChoices
---------------------------------
ChoiceID | ChoiceName
---------------------------------

I want to output a list of all the choices, and then the number of members who selected each choice. Here's my query:

SELECT C.ChoiceName, COUNT(M.ChoiceID)
FROM tblMembers M INNER JOIN tblChoices C
ON M.ChoiceID = C.ChoiceID
GROUP BY C.ChoiceName

The only problem is that if no members selected a certain choice, that choice doesn't show up in the list; I'd like it to show up with a 0 next to it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-31 : 14:26:53

Use an OUTER JOIN.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-31 : 14:28:50

RIGHT OUTER JOIN worked, thank you!

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-31 : 14:40:19

Actually, I have another problem...I just added a where clause to the query, and it stopped showing all the choices.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-31 : 14:41:42

You'll have to show us a data example as I can't see your system no matter how hard I squint.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-31 : 14:55:24

---------------------------------
tblMembers
---------------------------------
MemberID | ChoiceID | JoinDate
---------------------------------

---------------------------------
tblChoices
---------------------------------
ChoiceID | ChoiceName | Order
---------------------------------

@Month int,
@Year int
AS
BEGIN
SET NOCOUNT ON;

SELECT C.ChoiceName, COUNT(M.ChoiceID)
FROM tblMembers M INNER JOIN tblChoices C
ON M.ChoiceID = C.ChoiceID
WHERE (MONTH(M.JoinDate) = @Month) AND
(YEAR(M.JoinDate) = @Year)
GROUP BY C.ChoiceName, C.Order
ORDER BY C.Order ASC
END

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-31 : 15:09:14

Or maybe it's not the WHERE clause at all, could it have something to do with the fact that I added something to the GROUP BY clause?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-31 : 15:34:06

Where's the data example? Show us what the query is currently returning and then show us what it should return.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-31 : 15:55:23

Returns:

Dog 11
Cat 2
Fish 2
Giraffe 11
Bear 1
Lion 20

Should return:

Dog 11
Cat 2
Mouse 0
Fish 2
Whale 0
Bird 0
Giraffe 11
Bear 1
Frog 0
Lion 20

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-31 : 15:58:10

Why aren't you using an OUTER JOIN like I said in my first post?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-31 : 16:07:34

quote:
Originally posted by tkizer

Why aren't you using an OUTER JOIN like I said in my first post?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Oops, sorry, I copied that from my original post and forgot to change it back. Here's my new one:

@Month int,
@Year int
AS
BEGIN
SET NOCOUNT ON;

SELECT C.ChoiceName, COUNT(M.ChoiceID)
FROM tblMembers M RIGHT OUTER JOIN tblChoices C
ON M.ChoiceID = C.ChoiceID
WHERE (MONTH(M.JoinDate) = @Month) AND
(YEAR(M.JoinDate) = @Year)
GROUP BY C.ChoiceName, C.Order
ORDER BY C.Order ASC
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-31 : 16:12:17

Why do you have C.Order in the GROUP BY?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-31 : 16:21:30

quote:
Originally posted by tkizer

Why do you have C.Order in the GROUP BY?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Because if I don't, it gives me an error saying:

Column "tblChoices.Order" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-31 : 16:23:10

Don't just add columns to the GROUP BY to get rid of an error. It will not produce the correct results as you are seeing.

So if you removed the column from the GROUP BY, does it produce the correct results (although unsorted to your liking)?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-31 : 16:26:43

quote:
Originally posted by tkizer

Don't just add columns to the GROUP BY to get rid of an error. It will not produce the correct results as you are seeing.

So if you removed the column from the GROUP BY, does it produce the correct results (although unsorted to your liking)?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Hmmm, no, I didn't try that. I took out Order from the GROUP BY and ORDER BY clauses, and it still didn't give me what I wanted.

But once I took our the WHERE clause, it gave me what I wanted. So I guess it was the WHERE clause all along.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-31 : 16:30:57

You aren't telling us the whole story then.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-31 : 16:35:24

quote:
Originally posted by tkizer

You aren't telling us the whole story then.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Please tell me what else you'd like to know.

With the WHERE clause in there, it is showing the data, but not the data with 0 choices

Without the WHERE clause, it is showing all the data, even the data with 0 choices.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-31 : 16:43:34

Oh I see the issue now. You need to understand what an OUTER JOIN is doing. In your case, it is grabbing everything from Choices and then only the matches from Members. You are then trying to filter on Members. It is switching to an INNER JOIN due to the filter.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-31 : 16:51:16

quote:
Originally posted by tkizer

Oh I see the issue now. You need to understand what an OUTER JOIN is doing. In your case, it is grabbing everything from Choices and then only the matches from Members. You are then trying to filter on Members. It is switching to an INNER JOIN due to the filter.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Is there another way that I can filter the results?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-31 : 17:04:38

[code]SELECT c.ChoiceName,
COUNT(m.ChoiceID)
FROM tblMembers as m
RIGHT JOIN tblChoices AS c ON c.ChoiceID = m.ChoiceID
AND MONTH(m.JoinDate) = @Month
AND YEAR(m.JoinDate) = @Year
GROUP BY c.ChoiceName,
c.Order
ORDER BY c.Order[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources