SELECT using an ID from a SELECT 1

[JohnnyLong]

Hi,

My second Select is based on the results of the first Select.

SELECT rc_Contacts.ContactID, rc_Events.EventID, rc_Events.MeetingID

FROM rc_Contacts LEFT OUTER JOIN rc_Events ON rc_Contacts.ContactID = rc_Events.ContactID

WHERE rc_Events.MeetingID=@pKeyID

UNION

SELECT rc_Contacts.ContactID, rc_Events.EventID, rc_Events.MeetingID

FROM rc_Contacts INNER JOIN rc_Events ON rc_Contacts.ContactID = rc_Events.ContactID

WHERE rc_Events.MeetingID= 'First SELECT rc_Events.MeetingID'

How do I do this?

Thanks,

John

 

[ilyad]

I think something is wrong in the sample you posted. Your second select is almost the same as the first. What exactly you're trying to achieve?

 

[AlexCuse]

I think you should step back and explain what you are trying to accomplish, maybe showing some sample data and desired result. I don't get it (I am very easily confused though ;-) )

Ignorance of certain subjects is a great part of wisdom

 

[monksnake]

Based on what I understand, this:

WHERE rc_Events.MeetingID= 'First SELECT rc_Events.MeetingID'

would just become this:

WHERE rc_Events.MeetingID=@pKeyID

Since the only MeetingID returned from the "first" select would be @pKeyID





<.

 

[JohnnyLong]

Sorry, that wasn't very clear was it?

The first Select picks everything where the rc_Events.MeetingID = the passed parameter @pKeyID

I also need to Select all records where rc_Events.MeetingID = the rc_Events.MeetingID selected from the first select. I did a UNION query because that seemed easiest to me but it's really an OR as in

SELECT *, MeetingID
WHERE ID = 123456
OR
SELECT *
WHERE ID = MeetingID (from 1st Select)

Hope that's clearer.

John

 

[ilyad]

It looks like you simply want all your records twice in the result. Use ID = 123456 in both cases then.

 

[JohnnyLong]

Yes, you're right! been a long day. Should read

SELECT rc_Contacts.ContactID, rc_Events.EventID, rc_Events.MeetingID

FROM rc_Contacts LEFT OUTER JOIN rc_Events ON rc_Contacts.ContactID = rc_Events.ContactID

WHERE rc_Events.MeetingID=@pKeyID

UNION

SELECT rc_Contacts.ContactID, rc_Events.EventID, rc_Events.MeetingID

FROM rc_Contacts INNER JOIN rc_Events ON rc_Contacts.ContactID = rc_Events.ContactID

WHERE rc_Events.EventID= 'First SELECT rc_Events.MeetingID'

 

[ilyad]

Which SQL Server version? If 2005, would CTE work in this case?

http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/

 

[JohnnyLong]

We use SQL Server 7.

This is how my rc_Events data looks:

EventID MeetingID
12345 0
12346 12345
12347 12345
12348 12345
12349 0
12350 54321
12351 54321

I want to select the MeetingID records with the ID 12345 and the EventID with the ID 12345 using @pKeyID which will be any EventID the user selects.

So if I pass a @pKeyID of 12347 to the Stored proc I return the recs with a meetingID of 12345 and the record with the EventID 12345.

What's the best way to do this?

Thanks for your patience.

 

[ilyad]

Can you use table variable or temp table in SQL Server 7.0?

CTE in SQL Server 2005 looked like a good option. In prior versions you need to use table variable. But I don't work with SQL Server, so I don't know if it's available in this version.

 

[ilyad]

Here is a bit on the topic

http://tek-tips.com/viewthread.cfm?qid=1362204&page=2

but rather look it in BOL.

 

[gmmastros]

>> Can you use table variable or temp table in SQL Server 7.0?

Table variables didn't exist until SQL 2000, but temp tables exist in version 7.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Lamprey13]

Does this work?

DECLARE @T TABLE(EventID INT, MeetingID INT)
DECLARE @pKeyID INT

INSERT @T
SELECT 12345, 0
UNION SELECT 12346, 12345
UNION SELECT 12347, 12345
UNION SELECT 12348, 12345
UNION SELECT 12349, 0
UNION SELECT 12350, 54321
UNION SELECT 12351, 54321

SET @pKeyID = 12347

SELECT *
FROM @T
WHERE MeetingID = (SELECT TOP 1 MeetingID FROM @T WHERE EventID = @pKeyID)

-Ryan

 

[JohnnyLong]

Thanks for the input everyone.
Thanks Ryan, here is my query.

DECLARE @pKeyID int

SET @pKeyID = 12347

SELECT *
FROM rc_Events
WHERE MeetingID = (SELECT TOP 1 MeetingID FROM rc_Events WHERE EventID = @pKeyID)
UNION
SELECT *
FROM rc_Events
WHERE EventID = (SELECT TOP 1 MeetingID FROM rc_Events
WHERE EventID = @pKeyID)

this returns the 4 records I want.
I also tried

DECLARE @pKeyID int

SET @pKeyID = 12621387


SELECT *
FROM rc_Events
WHERE MeetingID = (SELECT TOP 1 MeetingID FROM rc_Events WHERE EventID = @pKeyID)
OR
EventID = (SELECT TOP 1 MeetingID FROM rc_Events WHERE EventID = @pKeyID)

which also works but is considerably slower.

John

 

[ilyad]

Why you didn't try through the temp variable for your inner select? Or there is no speed difference in SQL Server if we use the same query twice?