i have 2 tables i want ... - 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)

i have 2 tables i want ...

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2006-06-29 : 10:24:54

dear

I have 2 tables with name category and blogs,

category contains catid, cat_name

where blogs contains cat_name, blogname

i have a lot of rows in blogs table with different different cat_name

now i want cat_name from category table and total rows for that cat_name from blogs table

please help me

regards,
ASIF

Q
Yak Posting Veteran

76 Posts

Posted - 2006-06-29 : 10:33:30

Why using the table category when you already have cat_name in the table blogs???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 10:37:33

Try this code

SELECT		Category.Cat_Name,
		COUNT(*)
FROM		Category
LEFT JOIN	Blogs ON Blogs.Cat_Name = Category.Cat_Name
GROUP BY	Category.Cat_Name
ORDER BY	Category.Cat_Name

If your tables were normalized, you could use this instead

SELECT		Category.Cat_Name,
		COUNT(*)
FROM		Category
LEFT JOIN	Blogs ON Blogs.CatID = Category.CatID
GROUP BY	Category.Cat_Name
ORDER BY	Category.Cat_Name

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 10:38:12

quote:
Originally posted by Q

Why using the table category when you already have cat_name in the table blogs???

I think this is because some categories are not used in blogs table...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-30 : 01:07:20

To be really, really sure all categories are fetched, use this code (if there is not referential integrity between the tables)

SELECT		z.Cat_Name,
		COUNT(*)
FROM		(
			SELECT	Cat_Name
			FROM	Category
			UNION
			SELECT	Cat_Name
			FROM	Blogs
		)z
LEFT JOIN	Blogs ON Blogs.Cat_Name = z.Cat_Name
GROUP BY	z.Cat_Name
ORDER BY	z.Cat_Name

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources