Complicated 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

Complicated Query

Author

Topic

r000t
Starting Member

3 Posts

Posted - 2010-04-15 : 05:32:32

Hello,

Im new to queries and i'm trying to pull off a seemingly tricky one.

Somehow I need to 'merge' or 'combine' the two queries below into one:

[CODE]SELECT TopicName, Duration
FROM tblTopics
WHERE (TopicCode IN (@TopicCode1, @TopicCode2)) [/CODE]

[CODE]SELECT DateStarted, DateToEnd
FROM tblSubscriptions
WHERE (UserID = @UserID) AND (TopicCode IN (@TopicCode1, @TopicCode2))[/CODE]

At first glance I thought this was impossible to do but it's worth a try posting on a forum where people know alot more than me on the subject :)

Any ideas?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-15 : 05:36:13

Try this:

SELECT        t.TopicName, t.Duration, s.DateStarted, s.DateToEnd
FROM            tblTopics t
INNER JOIN      tblSubscriptions s
	ON             t.TopicCode = s.TopicCode
WHERE        (s.UserID = @UserID) AND (t.TopicCode IN (@TopicCode1, @TopicCode2))

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 05:39:21

is there any relationship between the tables? (is it topicCode?)

Then maybe it is:


SELECT
	t.TopicName
	, t.Duration
	, s.DateStarted
	, s.DateToEnd
FROM
	tblTopics t
	JOIN tblSubscriptions s ON s.TopicCode = t.TopicCode
WHERE
	t.topicCOde IN (@TopicCode1, @TopicCode2)
	AND s.UserID = @UserID

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

r000t
Starting Member

3 Posts

Posted - 2010-04-15 : 06:03:20

DBA In The Making:

This code worked absolutely perfectly! I'm impressed :)

Thanks for your quick response and reply.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-15 : 06:06:42