Using sample table name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using sample table name

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-21 : 06:02:47

Hi friends,

select m.firstname,d.CreationDateTime,d.DissId,d.groupId,
d.Userid,d.disstitle,d.dissdetails ,
(select count(Dissid) from comments where d.dissid=dissid
and deleted=0 and status=1 ) as NoOfComments

from Disscussions d
join member m on d.userid=m.userid
INNER JOIN (SELECT Dissid, MAX(CreationDateTime) AS LatestComment FROM Comments
where creationdatetime >= DATEADD(DAY, -6, GETDATE())
GROUP BY Dissid)c ON c.Dissid=d.Dissid
where d.groupid=5
and d.deleted=0 and d.Status=1
ORDER BY c.LatestComment DESC

Here i get firstname of the user who is discussed,in addition to that
i need the firstname of the user who is commented.(both from member table)
How can i get that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 06:33:27

show your sample data from tables and then explain what you want as output

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-10-21 : 06:54:35

try this assuming UserId column in Comments table


SELECT  m.firstname       ,
        d.CreationDateTime,
        d.DissId          ,
        d.groupId         ,
        d.Userid          ,
        d.disstitle       ,
        d.dissdetails     ,
        (SELECT COUNT(Dissid)
        FROM    comments
        WHERE   d.dissid=dissid
            AND deleted =0
            AND status  =1
        ) AS NoOfComments	,
        mc.firstname
FROM    Disscussions d
JOIN member m ON      d.userid=m.userid
INNER JOIN
                (SELECT Dissid,
                        MAX(CreationDateTime) AS LatestComment
                FROM    Comments
                WHERE   creationdatetime >= DATEADD(DAY, -6, GETDATE())
                GROUP BY Dissid
                )c
        ON      c.Dissid=d.Dissid
INNER JOIN Comments CM ON CM.Dissid = C.Dissid
		AND CM.LatestComment = C.LatestComment
INNER JOIN member mc ON      MC.userid=CM.userid
WHERE   d.groupid       =5
    AND d.deleted       =0
    AND d.Status        =1
ORDER BY c.LatestComment DESC

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-21 : 07:02:23

I had three tables disscussions,comments.member

Disscussions has fields -> dissid,groupid,userid,disstitle,dissdetails,creationdatetime
comments has fields -> commentid,userid,dissid,comments,creationdatetime
member has fields -> userid,firstname

select m.firstname,d.CreationDateTime,d.DissId,d.groupId,
d.Userid,d.disstitle,d.dissdetails ,
(select count(Dissid) from comments where d.dissid=dissid
and deleted=0 and status=1 ) as NoOfComments

from Disscussions d
join member m on d.userid=m.userid
INNER JOIN (SELECT Dissid, MAX(CreationDateTime) AS LatestComment FROM Comments
where creationdatetime >= DATEADD(DAY, -6, GETDATE())
GROUP BY Dissid)c ON c.Dissid=d.Dissid
where d.groupid=5
and d.deleted=0 and d.Status=1
ORDER BY c.LatestComment DESC

From the above query i get the username (who is disscussed in disscussion table)
In addition to that,i need the username of which user made last comment for that particular disscussion

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-21 : 07:14:42

My details are repeating When the disscussion has more than one comment .I need the last commented username with details only

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 07:19:56

[code]SELECT m.firstname,d.CreationDateTime,d.DissId,d.groupId,
d.Userid,d.disstitle,d.dissdetails ,
(select count(Dissid) from comments where d.dissid=dissid
and deleted=0 and status=1 ) as NoOfComments,
m1.firstname as LatestCommenter
FROM Discussions d
INNER JOIN member m
on m.userid=d.userid
INNER JOIN (SELECT c.*
FROM comments c
INNER JOIN (SELECT userid,dissid,MAX(createddatetime) as maxdate
FROM comments
GROUP BY userid,dissid)c1
ON c1.userid=c.userid
AND c1.dissid=c.dissid
AND c1.maxdate=c.createddatetime)cmt
ON cmt.dissid=d.dissid
INNER JOIN members m1
on m1.userid=cmt.userid[/code]

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-21 : 07:48:24

Visakh
I used your query ,the results are repeating.if one disscussion had more than one comment,i need the last commented username only.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 07:51:47

[code]SELECT m.firstname,d.CreationDateTime,d.DissId,d.groupId,
d.Userid,d.disstitle,d.dissdetails ,
(select count(Dissid) from comments where d.dissid=dissid
and deleted=0 and status=1 ) as NoOfComments,
m1.firstname as LatestCommenter
FROM Discussions d
INNER JOIN member m
on m.userid=d.userid
INNER JOIN (SELECT c.*
FROM comments c
INNER JOIN (SELECT userid,dissid,MAX(commentid) as maxcomment
FROM comments
GROUP BY userid,dissid)c1
ON c1.userid=c.userid
AND c1.dissid=c.dissid
AND c1.maxcomment=c.commentid)cmt
ON cmt.dissid=d.dissid
INNER JOIN members m1
on m1.userid=cmt.userid[/code]

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-21 : 07:57:17

Details are repeating when two different users are commented for one disscussion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 08:00:29

quote:
Originally posted by Kotti

Details are repeating when two different users are commented for one disscussion

modify like this

SELECT m.firstname,d.CreationDateTime,d.DissId,d.groupId,
d.Userid,d.disstitle,d.dissdetails ,
(select count(Dissid) from comments where d.dissid=dissid
and deleted=0 and status=1 ) as NoOfComments,
m1.firstname as LatestCommenter
FROM Discussions d
INNER JOIN member m
on m.userid=d.userid
INNER JOIN (SELECT c.*
            FROM comments c
            INNER JOIN (SELECT dissid,MAX(commentid) as maxcomment
                         FROM comments
                         GROUP BY dissid)c1
            ON  c1.dissid=c.dissid
            AND c1.maxcomment=c.commentid)cmt
ON cmt.dissid=d.dissid
INNER JOIN members m1
on m1.userid=cmt.userid

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-21 : 08:21:25

Thank you all,now i got the answer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 09:41:50

quote:
Originally posted by Kotti

Thank you all,now i got the answer

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources