Join using the same table - need help please

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)

Join using the same table - need help please

Author

Topic

legacyvbc
Starting Member

37 Posts

Posted - 2007-09-20 : 11:51:35

I have a table of stock tickers similar to the following:
TickerId Ticker SectorId HedgeId
1 MSFT 1 1
2 INTC 1 1
3 GE 2 1
4 T 3 1
5 S5TECH 1 2
6 S5COND 2 2
7 S5TELE 3 2
8 XLK 1 3
9 XLD 2 3
10 TTH 3 3

I'm trying to create a query that will return all tickerId's that have a hedgeid of 1 joined with all tickers that have a hedgeid 3 and have the same industry id. So in the above data the query would return:
TickerId Ticker SectorId HedgeId TickerId2 Ticker
1 MSFT 1 1 8 XLK
2 INTC 1 1 8 XLK
3 GE 2 1 9 XLD
4 T 3 1 10 TTH

I can't seem to figure out how to do this correctly but I can't imagine it is difficult. Any help is greatly appreciated.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-20 : 12:23:36

Here ya go. This is often called a self-join.

SELECT a.tickerID,a.Ticker,a.sectorId,a.hedgeid,b.TickerID,b.Ticker
FROM
tbl a
inner join
tbl b
on
a.sectorid = b.sectorid
where
a.hedgeid = 1
and b.hedgeid = 3

Jim

legacyvbc
Starting Member

37 Posts

Posted - 2007-09-20 : 15:05:16

awesome! thank you so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources