Solved

Complex ms sql query finding column count and latest column value

Posted on 2013-06-03
7
265 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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 500 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Select Query help 3 31
How to select Week Start and Week End dates 5 24
Test a query 23 17
Service Statictic 11 7
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article I will describe the Detach & Attach 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.
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 shrink a transaction log file down to a reasonable size.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now