Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Complex ms sql query finding column count and latest column value

Posted on 2013-06-03
7
Medium Priority
?
275 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

636 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