where clause across two tables

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

SQL Server 2000 Forums

Transact-SQL (2000)

where clause across two tables

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-08-21 : 04:52:32

Im attempting to create a SP that grabs all 'active topics' in a snitz forum such as this one.

I want to show all topics that the user has either created or posted in. I only want a topic to show once, no matter how many times they have posted in it.

This query just queries the TOPIC table. I also have a FORUM_REPLY table that keeps all the replies. So I need to somehow include this table in the query and add

WHERE R_AUTHOR = @userID

or something to that effect into the query.

any suggestions or help greatly appreciated.

thanks alot

mike123


CREATE PROCEDURE select_activeThreads 411
		(
                 @userID int
		)

AS SET NOCOUNT ON

SELECT TOP 12 T_STATUS, CAT_ID, FORUM_ID, TOPIC_ID, T_VIEW_COUNT, T_SUBJECT, T_AUTHOR, 
T_REPLIES, T_LAST_POST, T_DATE, T_LAST_POST_AUTHOR,tb1.nameOnline, tb2.nameOnline AS LAST_POST_AUTHOR_NAME 
FROM FORUM_TOPICS topics JOIN tblUserDetails tb1 on tb1.userID = TOPICS.T_AUTHOR 
JOIN tblUserDetails tb2 on tb2.userID = TOPICS.T_LAST_POST_AUTHOR 
WHERE T_AUTHOR = @userID ORDER BY TOPICS.T_LAST_POST desc

GO

<edit> to fix display </edit>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-21 : 12:51:59

Have you tried using a UNION yet?

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-08-21 : 15:14:55

No, I havent tried using a UNION yet. I thought I might have to, but i'm a bit unsure on how to approach this one.

Thanks
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-08-21 : 17:07:21

I've been trying to get this to work with a UNION for the last little bit here, to no prevail. I am wondering if a UNION is really what I need, I'll explain my situation and whats happening a little better so its easier to see.

My two tables are as follows:

FORUM_TOPIC

CAT_ID (tinyint)
FORUM_ID (smallInt)
TOPIC_ID (int)
T_STATUS (tinyint)
T_SUBJECT (varchar50)
T_MESSAGE (text)
T_AUTHOR (int)
T_REPLIES (int)
T_VIEW_COUNT (int)
T_LAST_POST (datetime)
T_DATE (datetime)
T_LAST_POST_AUTHOR (int)

FORUM_REPLY

CAT_ID (tinyint)
FORUM_ID (smallInt)
TOPIC_ID (int)
REPLY_ID (int)
R_AUTHOR (int)
R_MESSAGE (text)
T_VIEW_COUNT (int)
R_DATE (datetime)

I want to select each row in the FORUM_TOPICS table where either T_AUTHOR = @userID (parameter passed to the SP). I also want to select each row in the FORUM_TOPICS that corresponds to the reply in FORUM_REPLY where R_AUTHOR = @userID.

I hope this makes things a little more clear. Am I still looking at a UNION, or maybe some complex JOINS?

Thanks alot for any help

Mike123

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-22 : 05:47:30

You will have problems with UNION because there are different numbers of columns in both tables. I'm surprised that the structure is held like this, as it seems to be differentiating betweeen Forum Topics and Replies, which I would have expected to all come under a more general heading of Posts.

Anyway, you could either select a subset of your data with a union, such as


SELECT
  TOPIC_ID, T_AUTHOR as 'Author' 
FROM
  FORUM_TOPIC
UNION
SELECT
  TOPIC_ID, R_AUTHOR as 'Author'
FROM
  FORUM_REPLY

Or you would need to do some kind of left join to a derived table based on T author id = R author id, I expect.

-------
Moo. :)

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-08-22 : 11:13:38

1.) Create a temptable with topicID, and T_OR_R_Author columns.

2.) insert all records from both tables where the userid = Paramter.

3.) Select distinct records from the temp table.

4.) Do a select wich Inner joins the temp table with you user details, and topic table.

I just had a similar query for our timekeeper application. This worked well. If I get time today, I will write the query and post it.

thanks,

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-22 : 11:55:58

[code]SELECT TOP 12 topic_id

FROM (

SELECT topic_id, MAX(t_date) 'LastPostperTopic'

FROM (
SELECT topic_id, t_date
FROM FORUM_TOPIC
WHERE t_author = < parameter >

UNION ALL

SELECT topic_ID, R_date
FROM FORUM_REPLY
where r_author = < parameter >
) x

GROUP BY topic_id
)

ORDER BY LastPostperTopic DESC[/code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-08-22 : 15:26:38

quote:
Originally posted by TSQLMan

1.) Create a temptable with topicID, and T_OR_R_Author columns.

2.) insert all records from both tables where the userid = Paramter.

3.) Select distinct records from the temp table.

4.) Do a select wich Inner joins the temp table with you user details, and topic table.

I just had a similar query for our timekeeper application. This worked well. If I get time today, I will write the query and post it.

thanks,

that would really help alot if you could do that..

I just tried drymchaser's approach, what he wrote worked but the thing is I need to select every column in the FORUM_TOPICS table, and I dont think I can modify it and get that to work.

thanks alot

mike123

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-08-22 : 15:39:06

[code]

SELECT B.<column1>,B.<column2>,....,B.<columnn> FROM FORUM_TOPIC B
INNER JOIN
(

SELECT TOP 12 topic_id

FROM (

SELECT topic_id, MAX(t_date) 'LastPostperTopic'

FROM (
SELECT topic_id, t_date
FROM FORUM_TOPIC
WHERE t_author = < parameter >

UNION ALL

SELECT topic_ID, R_date
FROM FORUM_REPLY
where r_author = < parameter >
) x

GROUP BY topic_id
)

ORDER BY LastPostperTopic DESC

)

A

ON B.topic_id=A.topic_id

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources