SELECT Statement w/Inner Join Question...

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

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT Statement w/Inner Join Question...

Author

Topic

kwilliams

194 Posts

Posted - 2005-02-14 : 10:42:38

I'm trying to create a webpage that displays a list of candidates for a local election. It uses the following 3 related tables:
1) dbo.ElectionTotals (ET)
2) dbo.ElectionCandidates (EC)
3) dbo.Voting_Sites (VS)

NOTE: The related columns are 'ET.CID = EC.CID' and 'ET.Precinct_ID = VS.Precinct_ID'.

This is the SELECT statement that I'm using:

SELECT EC.CID, LName, FName, EC.Office, ET.CID, Votes, ET.Precinct_ID
FROM dbo.ElectionCandidates AS EC INNER JOIN dbo.ElectionTotals AS ET ON EC.CID = ET.CID INNER JOIN dbo.Voting_Sites AS VS ON ET.Precinct_ID = VS.Precinct_ID
ORDER BY EC.Office, LName

I'm sure that you can see that this results in duplicate results because dbo.ET has its data formatted like this:
Precinct_ID CID Votes
1 1 5
1 2 10
1 3 15
1 4 20

This data results in the following data display:
Office Sought #1
Candidate #1

Office Sought #1
Candidate #2

Office Sought #2
Candidate #3

Office Sought #2
Candidate #4

When I want it to be displayed like this:
Office Sought #1
Candidate #1
Candidate #2

Office Sought #2
Candidate #3
Candidate #4

So if anyone can see how I need to adjust my SQL SELECT statement for it to work properly, please let me know. Thanks a bunch.

KWilliams

Katherine Williams
-------------------
It's the end of the world as we know it...and I feel fine

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2005-02-14 : 11:18:12

so you want to GROUP BY office?

kwilliams

194 Posts

Posted - 2005-02-14 : 11:29:58

That's sounds like a suggestion that would take me in the right direction, but after adding "GROUP BY EC.Office" to the end of the statement, I received the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'EC.CID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So then I tried changing the SELECT statement to include a GROUP BY clause like this:

SELECT     EC.CID, EC.LName, EC.FName, ET.CID AS Expr1, ET.Votes, ET.Precinct_ID, COUNT(*) AS Office
FROM         ElectionCandidates EC INNER JOIN
                      ElectionTotals ET ON EC.CID = ET.CID AND EC.CID = ET.CID AND EC.CID = ET.CID INNER JOIN
                      Voting_Sites VS ON ET.Precinct_ID = VS.Precinct_ID
WHERE EC.CID = ET.CID
GROUP BY EC.Office

...but it results in the same error message. Is there something that I'm missing? I haven't worked much with GROUPING so I'm kind of lost when it comes to using this clause. Thanks again for any help.

KWilliams

Katherine Williams
-------------------
It's the end of the world as we know it...and I feel fine

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-14 : 11:37:25

You need to GROUP BY exactly whats in the SELECT list excluding any functions (SUM,COUNT,MAX....etc). Look up GROUP BY in BOL

Try
GROUP BY EC.CID, EC.LName, EC.FName, ET.CID, ET.Votes, ET.Precinct_ID

Andy

Beauty is in the eyes of the beerholder

kwilliams

194 Posts

Posted - 2005-02-14 : 11:39:10

Ok, I'll give it a try. Thanks for your help.

Katherine Williams
-------------------
It's the end of the world as we know it...and I feel fine

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-14 : 11:40:09

On another note why are you doing this

quote:

ON EC.CID = ET.CID AND EC.CID = ET.CID AND EC.CID = ET.CID

You can also get rid of your WHERE clause as the JOIN does that for you

Andy

Beauty is in the eyes of the beerholder

kwilliams

194 Posts

Posted - 2005-02-14 : 11:41:59

That was just a blip on my posting. My fault. And I'll remove the WHERE clause. Thanks.

Katherine Williams
-------------------
It's the end of the world as we know it...and I feel fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources