Alternative to subquery??? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Alternative to subquery???

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2004-12-27 : 18:57:57

Guys,
There must be a way to get rid of this subquery...

CREATE TABLE #tempMe (temp_id INT, temp_name VARCHAR(10))

INSERT INTO #tempMe
VALUES(1,'yak')
INSERT INTO #tempMe
VALUES(1,'foo')
INSERT INTO #tempMe
VALUES(2,'foo')

Now, I want to return all columns for any id that has an occurence temp_name = 'yak'. For example:

SELECT * FROM #tempMe WHERE temp_id IN (SELECT temp_id FROM #tempMe WHERE temp_name = 'yak')

Can this not be accomplished without a subquery?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-27 : 19:04:02

SELECT t1.temp_id, t2.temp_name
FROM #tempMe t1
INNER JOIN #tempMe t2
ON t1.temp_id = t2.temp_id
WHERE t1.temp_name = 'yak'

Tara

nathans
Aged Yak Warrior

938 Posts

Posted - 2004-12-27 : 19:18:57

duh!!! Tara, nice work. Quick too! Thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources