?
Solved

Complex ms sql query finding column count and latest column value

Posted on 2013-06-03
7
Medium Priority
?
279 Views
Last Modified: 2013-06-03
I have simplified the model of what I'm trying to do so hopefully this makes sense.
I have three tables  basically two pieces of information and a join.

tbl_users    tbl_join_users_comments     tbl_comments
pk_user      pk_join                                  pk_comment
user           pk_user                                 comment
                  pk_comment

So what I want is a view that returns a column with the user,   a column with the count of the number of joins for that user and a comment,   and a column with the LAST entered comment joined to the user.
0
Comment
Question by:Bastyon
  • 3
  • 2
  • 2
7 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39216779
how to determine the last entered comment for the user... the pk_comment is an identity field???
0
 

Author Comment

by:Bastyon
ID: 39216800
yes pk_comment is id field   as is pk_user  and pk_join
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39216825
Your desired output is not clear specifically which comment do you want if the user has multiple?  Are you expecting multiple records per user?
0
Technology Partners: 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!

 

Author Comment

by:Bastyon
ID: 39216857
No  im expecting one column per user that has the users name..the number of comments there are  and the last entered comment for the users.

So for example

if the tbl_user  had the values

pk_user   user
1             Bob
2             Joe

and the join table had the values

pk_join    pk_user   pk_comment
1              1             1
2               2            2
3              1             3


and the comment table had the values

pk_comment    comment
1                       "Bobs first comment"
2                       "Joes only comment"
3                       "Bobs second comment"

the view results would be

user        comment_count          comment
Bob         2                                 "Bobs second comment"
Joe           1                                 "Joes only comment"
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39216858
try the below code
create view vTest
as
BEGIN
 WITH user_comment_Count AS
(
select count(1) as count_user_comment ,U.[user],u.pk_user
from tbl_join_users_comments UC
JOIN tbl_users U
ON UC.pk_user = U.pk_user
group by U.pk_user
),All_comments AS
(
  select C.comment,U.[user],u.pk_user,C.pk_comment
from tbl_comments C
JOIN  tbl_join_users_comments UC
ON UC.pk_comment= C.pk_comment
JOIN tbl_users U
on U.pk_user = UC.pk_user
),last_comment AS
(
  select comment,[user],A.pk_user,A.pk_comment
from all_comments A
,(
select max(pk_comment) pk_comment,pk_user from all_comments 
group by pk_user
) A1
where A.pk_comment = A1.pk_comment
and a.pk_user = a1.pk_user
)
select ucc.[user],ucc.count_user_comment,lc.comment
from user_comment_Count UCC
JOIN last_comment LC
ON ucc.pk_user = lc.pk_user
END

Open in new window

0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 39216886
try something like...

SELECT A.pk_user, A.CommentCount, C.comment AS LastComment
FROM (
SELECT U.pk_user, COUNT(*) AS CommentCount, MAX(C.pk_comment) AS LastCommentID
FROM tbl_Users AS U
INNER JOIN tbl_join_users_comments AS UC
   ON U.pk_user = UC.pk_user
INNER JOIN tbl_comments AS C
   ON UC.pk_comment = C.pk_comment
GROUP BY pk_user) AS A
INNER JOIN tbl_comments AS C
   ON A.LastCommentID = C.pk_comment
0
 

Author Closing Comment

by:Bastyon
ID: 39217217
This does exactly what I want it to.  Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

864 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