Try exists instead

WHERE event_type_id=2 AND version=597 AND tempus.station_id in

(select distinct station_id from events where event_type_id=5)

WHERE event_type_id=2 AND version=597

AND EXISTS (select * from events as p9 where p9.event_type_id=5 and tempus.station_id =...

Also asked here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87117

And if you prefer just one table scan,

SELECT      [User],

            MAX(CASE WHEN Test = 'PreTst' THEN Grade END) AS PreTst_Grade,

            MAX(CASE WHEN Test =...

http://www.mcse.ms/message2435438.html

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

For XML Path is avaible first with SQL Server 2005.

Please, please, please try the algorithm I posted earlier.

It will give you the "starting" category and all children.

I wonder about the speed for your correlated subquery versus your patented triangular join?

With a larger table than below...

-- Prepare sample data

DECLARE    @Sample TABLE (JobNo...

Please post expected output, based on previous posted sample data.

Okay..

Peter Larsson

Or the signature below my responses? It's the coordinates for my new house!

If someone gets mad at me and want to launch a missile...

Jeff, don't be sad.

Vamshi hasn't told us how he wants the expected result to be, not formatted.

My code above produces (with small alterations to fit Vamshis envionment) all children with...

CREATE FUNCTION DBO.FNGETHIERARCHY 

               (@CategoryID INT)

RETURNS @Peso TABLE (@CategoryID INT, @CategoryName VARCHAR(100), @Depth INT)

AS

  BEGIN

    DECLARE  @Generation INT

Same question again?

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=381713

I think you will be better served if you paid attention to the suggestion you are given, instead of just asking same question again.

If they are some kind of "suborders", it would be better to number them in sequence and still have "parent order" intact.

This would give you only two levels at any...

Is there a maximum depth of levels?

If so, you can write the query with a lot of LEFT JOINs.

It not, no you can't do this with a single query in...