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
Solved

Complex ms sql query finding column count and latest column value

Posted on 2013-06-03
7
269 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSIS On fail action 5 37
Powershell v3 - SQLCMD 3 26
SQL Quer 4 21
How can I get the entire database script? 7 12
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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…

809 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