Need help with SQL query - 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

Need help with SQL query

Author

Topic

manderson
Starting Member

6 Posts

Posted - 2010-03-23 : 13:48:27

Here is my table setup:

Topics
-TopicID
-Message
-UserID
-Date

Replies
-ReplyID
-Message
-UserID
-TopicID
-Date

Users
-UserID
-Username

I've made a forum and what I'd like to do is display all the topics that've been started by (for example) UserID 1 and UserID 2, along with all of the topics that've been replied to by UserID 1 and UserID 2.

This is how I'd like the results to look (assume UserID 1 uses Username_1 and UserID 2 users Username_2):

Topic example A, started by Username_1 on 01/01/2010
Topic example B, started by Username_4 on 01/02/2010 and replied to by Username_2 on 01/03/2010
Topic example C, started by Username_2 on 02/01/2010
Topic example D, started by Username_5 on 02/02/2010 and replied to by Username_1 on 02/03/2010

I hope that's not too confusing. I'd really appreciate your help. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 13:52:58

whats the order in which you want results? based on topic,date or user?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manderson
Starting Member

6 Posts

Posted - 2010-03-23 : 14:00:40

I'd like to give the user the option of sorting by topic date or reply date (I would use a select form -- I've done it in the past, so I'm hoping I'd also be able to do it here by using "ORDER BY " & strOrderBy).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 14:09:25

something like


SELECT required columns..
FROM Users u
LEFT JOIN Topic t
ON t.UserID=u.UserID
LEFT JOIN REplies r
ON r.TopicID = t.TopicID
WHERE t.UserID=@UserID
OR r.UserID=@UserID
ORDER BY.... your condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manderson
Starting Member

6 Posts

Posted - 2010-03-23 : 15:22:30

Thanks, visakh16, that works great except for one small problem. If UserID 1 wrote a topic which received 2 replies, the topic shows up 2 times in the query. And if UserID 1 replied to another topic 2 times, the topic shows up 2 times.

I made a few minor adjustments, but here is what I have so far:

strSQL = "SELECT u.UserID, u.Username, t.ID, t.Message, t.UserID, t.Date, r.UserID, o.Username " & _
"FROM Users u LEFT JOIN Topics t ON t.UserID = u.UserID LEFT JOIN Replies r ON r.TopicID = t.TopicID " & _
"LEFT JOIN Users o ON r.UserID = o.UserID WHERE t.UserID IN (1,2) OR r.UserID IN (1,2) ORDER BY t.Date DESC"

What should I do to solve this? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 00:20:57

[code]SELECT required columns..
FROM Users u
LEFT JOIN Topic t
ON t.UserID=u.UserID
LEFT JOIN REplies r
ON r.UserID= u.UserID
WHERE t.UserID IS NOT NULL
OR r.UserID IS NOT NULL
ORDER BY.... your condition
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources