Odd results from query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Odd results from query

Author

Topic

jbezanson
Starting Member

35 Posts

Posted - 2005-07-27 : 10:49:18

I have 3 tables: Catalog_Content, Catalog_Groups, and Catalog_Item

I want to display the number of Groups and Items for each Catalog in Catalog_Content.

There is only one Catalog in Catalog_Content: Cat_NameA=Products, Cat_ID=1.

When I run my query it is giving 30 Groups and 30 Items. But there are only 10 Groups and 3 Items. It is multiplying them together for some reason and I can't figure out why. I have tested with other tables and columns and it IS multiplying them.

Here is my query:

SELECT Cat_NameA, c.Cat_ID, count(g.Cat_Group_ID) Groups, count(Cat_Item_ID) Items
FROM Catalog_Groups g, Catalog_Content c, Catalog_Item i
WHERE c.Cat_ID = '1'
GROUP BY Cat_NameA, c.Cat_ID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-27 : 10:53:34

You need to relate theree tables by keyfield
Post the table structures

Madhivanan

Failing to plan is Planning to fail

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 11:30:53

quote:
Originally posted by madhivanan

You need to relate theree tables by keyfield
Post the table structures

Madhivanan

Failing to plan is Planning to fail

That was a bit meagre, the poster is probably a SQL beginner, don't You think ?!

.hint #2
Look for "INNER JOIN" in BOL.

rockmoose

jbezanson
Starting Member

35 Posts

Posted - 2005-07-27 : 11:56:04

Catalog_Content
--------------------
Cat_ID(PK) varchar 10
Cat_NameA varchar 250

Catalog_Groups
--------------------
Cat_Group_ID(PK) varchar 10
Cat_ID varchar 10
Group_LabelA varchar 100

Catalog_Item
-------------------------
Cat_Item_ID(PK) varchar 10
Cat_ID varchar 10
Item_NameA varchar 250

All 3 tables are 'joined', related by Cat_ID(FK)

Results I want(should be)
-----------------
Catalog Name | Catalog Id | # of Groups | # of Items
Products 1 10 3

Results I am getting (wrong)
-----------------
Catalog Name | Catalog Id | # of Groups | # of Items
Products 1 30 30

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 12:01:42

>> All 3 tables are 'joined', related by Cat_ID(FK).

Yes, but You have to specify those relations in the SQL query.
( see INNER JOIN in BOL )
You listed the tables, but did not relate them, You are effectively doing a cross join.

SELECT Cat_NameA, c.Cat_ID, count(g.Cat_Group_ID) Groups, count(Cat_Item_ID) Items
FROM
Catalog_Groups g
INNER JOIN Catalog_Content c ON g.<relatedcolumns> = c.<relatedcolumns>
INNER JOIN Catalog_Item i ON c.<relatedcolumns> = i.<relatedcolumns>
WHERE c.Cat_ID = '1'
GROUP BY Cat_NameA, c.Cat_ID

ok ??

rockmoose

jbezanson
Starting Member

35 Posts

Posted - 2005-07-27 : 12:09:24

So here is what I have now:

[CODE]
SELECT Cat_NameA, c.Cat_ID, count(g.Cat_Group_ID) Groups, count(Cat_Item_ID) Items
FROM Catalog_Groups g
INNER JOIN Catalog_Content c ON g.Cat_ID = c.Cat_ID
INNER JOIN Catalog_Item i ON c.Cat_ID = i.Cat_ID
WHERE c.Cat_ID = '1'
GROUP BY Cat_NameA, c.Cat_ID
[/CODE]

But I am still getting the same results.

Why are they multiplying? I am using the Query Analyzer on SQL Server 2000

Windows Server 2003 Enterprise
Dual Xeon CPUs
2 Gb DDR
Asp.Net 1.1
SQL Server 2000
Visual Studio.Net 2003

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 12:19:02

SELECT Cat_NameA, c.Cat_ID, count(DISTINCT g.Cat_Group_ID) Groups, count(Cat_Item_ID) Items

Run the query without the GROUP BY first, so that You can see the data it is grouping,
it might help You understand what is going on.

rockmoose

jbezanson
Starting Member

35 Posts

Posted - 2005-07-27 : 12:21:45

Adding DISTINCT worked. I tried removing the GROUP BY and got an error:

Server: Msg 8118, Level 16, State 1, Line 1
Column 'c.Cat_NameA' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'c.Cat_ID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Thanks for your help.

Cheers

Windows Server 2003 Enterprise
Dual Xeon CPUs
2 Gb DDR
Asp.Net 1.1
SQL Server 2000
Visual Studio.Net 2003

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 12:25:07

Forgot to mention, that if You remove the group by, you can't have the aggregates sum, count ...

without the group by it's just:
SELECT Cat_NameA, c.Cat_ID, g.Cat_Group_ID, Cat_Item_ID

rockmoose

jbezanson
Starting Member

35 Posts

Posted - 2005-07-27 : 12:31:46

Thank you

I have a greater understanding now!

Thanks again.

Windows Server 2003 Enterprise
Dual Xeon CPUs
2 Gb DDR
Asp.Net 1.1
SQL Server 2000
Visual Studio.Net 2003

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources