Solved

Complex ms sql query finding column count and latest column value

Posted on 2013-06-03
7
270 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 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