Inner Query Join / Count - 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 2005 Forums

Transact-SQL (2005)

Inner Query Join / Count

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-11-10 : 04:00:36

Hi,

Can you please help me get the following data:

- All of TABLE A
- Count of TABLE B row with matching ID
- The "Username" column of one Row of TABLE B with the newest date

Table A: Topics_Category
Table B: Topics_Discussion

Topics_Category A
- CategoryID (int) (pk)

Topics_Discussion B
- TopicID (int) (pk)
- CategoryId (int) (fk)
- DateAdded (DateTime)
- Username (varchar)

I need ALL rows in Category with a join for the count of rows in Discussion (b) with matching CategoryId AND the "Username" of the record with the newest date matching the CategoryId for each row.

So I want to see the count and "username" column for each row of categories (one "username" per row).

I guess I'd need to use an inner join for each of those two pieces of data? Or should I use a subquery?

Thanks.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-11-10 : 04:12:37

SELECT
USERNAME, COUNT(*)
FROM
Topics_Category A
INNER JOIN
Topics_Discussion B
ON
A.CATEGORYID = B.CATEGORYID
GROUP BY
USERNAME
ORDER BY
DATEADDED DESC

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-10 : 04:16:05

When you want All from Table A and only matching rows from Table B use LEFT OUTER JOIN

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-11-10 : 04:31:07

quote:
SELECT
USERNAME, COUNT(*)
FROM
Topics_Category A
INNER JOIN
Topics_Discussion B
ON
A.CATEGORYID = B.CATEGORYID
GROUP BY
USERNAME
ORDER BY
DATEADDED DESC

A couple problems w/that query:

- it returns all rows from B; I just want the count of rows in B.
- the "order by" applies the filter to table A.
- when I execute the below query, I'm getting duplicate rows from A.


SELECT A.*, A.CategoryId
,B.UserId, B.CategoryId
-- , COUNT(*) ???
FROM
	[wisetopic_topics_category] A
INNER JOIN
	[wisetopic_topics_discussion] B
ON
	A.CATEGORYID = B.CATEGORYID
ORDER BY
	B.DATEADDED DESC

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-11-10 : 04:32:54

PS - I'm pretty sure the solution will involve a sub query to get the count of discussions for each category row.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 04:51:09

did you mean this?

SELECT CategoryID,Username,,DiscussionCount
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY c.CategoryID ORDER BY d.DateAdded DESC) AS Seq,c.CategoryID,d.Username,d.DateAdded,
COUNT(TopicID) OVER (PARTITION BY c.CategoryID) AS DiscussionCount
FROM Topics_Category c
INNER JOIN Topics_Discussion d
ON d.CategoryID=c.CategoryID

)t
WHERE Seq=1

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-11-10 : 06:23:44

Hi Visakh,

Looks like this would work, except the "TopicId" column (count(TopicId)) is part of table [WiseTopic_Topics_Discussion] D. How would I rewrite this to fix that problem?

SELECT CategoryID,UserId,DiscussionCount
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY c.CategoryID ORDER BY d.DateAdded DESC)
AS Seq,c.CategoryID,d.Userid,d.DateAdded,
COUNT(TopicID) OVER (PARTITION BY c.CategoryID) AS DiscussionCount
FROM WiseTopic_Topics_Category c
INNER JOIN WiseTopic_Topics_Discussion d
ON d.CategoryID=c.CategoryID

)t
WHERE Seq=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 06:33:04

[code]SELECT CategoryID,UserId,DiscussionCount
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY c.CategoryID ORDER BY d.DateAdded DESC)
AS Seq,c.CategoryID,d.Userid,d.DateAdded,
COUNT(d.TopicID) OVER (PARTITION BY c.CategoryID) AS DiscussionCount
FROM WiseTopic_Topics_Category c
INNER JOIN WiseTopic_Topics_Discussion d
ON d.CategoryID=c.CategoryID

)t
WHERE Seq=1[/code]

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-11-11 : 01:35:34

Wow, "PARTITION" huh. Now that's a fancy new SQL 2K5 function I haven't used yet.

I had to look up the defintion; its actually very cool:

"The new ranking functions in SQL Server 2005 let us quickly calculate each row's ranking within a set based on a partition and an ordering. Think of the partition as almost like a GROUP BY, where you the use of the word "per" in your specifications often indicate which columns you are grouping or partitioning on."

So it basically replaces having to write those bloated sub queries every time you want to retrieve a count or calculate some stats. Very handy! I always learn something new when I get my posts answered by you, Visakh.

BTW the query worked great. Thanks buddy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 01:51:00

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources