?
Solved

SQL Query to return Count in Table 2 while also returning the row in Table 1.

Posted on 2010-01-07
8
Medium Priority
?
268 Views
Last Modified: 2012-05-08
I have 2 tables in which im looking for a query using a Join to return the record in Table 1 with a count of all the related rows in Table 2.

Table 1: Content
----------------------------
ContentID
ContentName
ContentDesc

Table 2: UserComments
----------------------------
CommentID
ContentID
UserName
Comment

The join would be on ContentID in both tables.

Im trying to return 1 row with 4 Columns. ContentID,ContentName,ContentDesc,CommentCount

CommentCount would = Count(*) as CommentCount

Thanks
0
Comment
Question by:maddhacker24
8 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 26204253
select ContentID,ContentName,ContentDesc,CommentCount
  from Table_1 s t1
  join (select ContentID,count(ContentID) as CommentCount from Table_2) as t2
    on t1.ContentID = t2.ContentID
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26204259
select t1.ContentID,ContentName,ContentDesc,CommentCount
  from Table_1 as t1
  join (select ContentID,count(ContentID) as CommentCount from Table_2) as t2
    on t1.ContentID = t2.ContentID
0
 
LVL 5

Accepted Solution

by:
fhillyer1 earned 200 total points
ID: 26204263
select a.contentid, a.contentName, a.contentdesc, (select count(commentid) from UserComments where contentid = a.contentid) 'Comment Count' from Content A
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:ClarkFilter
ID: 26204278
This one I just whipped up in Access, but I think the solution in SQL 2008 will be the same or very similar.  I think this may be what you are looking for.  The attached file is a graphical representation of the query in Access 2007.

SELECT Content.ContentID, Content.ContentName, Content.ContentDesc, Count(UserComments.Comment) AS CommentCount
FROM Content LEFT JOIN UserComments ON Content.ContentID = UserComments.ContentID
GROUP BY Content.ContentID, Content.ContentName, Content.ContentDesc;

query.png
0
 

Author Closing Comment

by:maddhacker24
ID: 31674239
Thanks...Exactly what I was looking for. The previous persons answer was causing a group by aggregate function error.  Yours did the trick.

Thanks again.

0
 
LVL 41

Expert Comment

by:Sharath
ID: 26206574
seems like you didn't try my suggestion. Anyway let's move on....
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26207427
Sharath,
you forgot the group by in your subquery so your query probably did not work as written.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26207502
yes CGLuttrell. i overlooked on that.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question