my query is wrong - 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)

my query is wrong

Author

Topic

jbezanson
Starting Member

35 Posts

Posted - 2005-08-10 : 09:25:06

Here is my query:


SELECT c.Cat_NameA,c.Cat_ID,Count(DISTINCT g.Cat_Group_ID) Groups,Count(DISTINCT i.Cat_Item_ID) Items
FROM Catalog_Content c, Catalog_Groups g, Catalog_Item i
WHERE c.Site_ID = 'cbs'
GROUP BY c.Cat_NameA, c.Cat_ID
ORDER BY c.Cat_NameA

and here are my results:

-------------------------------------
Cat_NameA | Cat_ID | Groups | Items |
-------------------------------------
Products 1 11 3
test 2 11 3

There are 2 catalogs in my DB, Products and test. test has 1 group with 0 items and Products has 10 grouops with 3 items.

How do I change my query to get these results?

-------------------------------------
Cat_NameA | Cat_ID | Groups | Items |
-------------------------------------
Products 1 10 3
test 2 1 0

Thanks,

Justin

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 09:32:46

You posted only expected data
Post some sample data

Madhivanan

Failing to plan is Planning to fail

jbezanson
Starting Member

35 Posts

Posted - 2005-08-10 : 09:41:13

Sorry I just realized that the desired results won't be possible with my current database structure.

Thanks anyways

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