SQL Server Forums - Having a relationship with oneself

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

Old Forums

CLOSED - General SQL Server

Having a relationship with oneself

Forum Locked

Printer Friendly

Author

Topic

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/31/2003 : 07:57:30

Zimon writes "I have a table of items as follows:
Item_ID Item_Name
1 Pish
2 Posh
3 Pash

I have a table of associations as follows:
Item1_ID Item2_ID
1 2
2 3

I need the output:
First_Item Second_Item
Pish Posh
Posh Pash

But after days of trying and internet searching I have no idea how to structure the SQLServer query. Please can you help or confirm that this is an imposibility.

Zimon"

robvolk
Most Valuable Yak

USA
15559 Posts

Posted - 10/31/2003 : 07:59:02

<wiping away tears of laughter>

Try this:

http://www.sqlteam.com/item.asp?ItemID=8866

</wiping away tears of laughter>

Although you can probably get away with:

SELECT A.Item_Name, B.Item_Name
FROM myTable A INNER JOIN Association C ON A.Item1_ID
INNER JOIN myTable B ON C.Item2_ID=B.Item_ID

And if you REALLY meant what the title states, then perhaps this is what you need:

http://www.amazon.com/exec/obidos/tg/detail/-/0517886073/102-8441179-3137755?v=glance

Edited by - robvolk on 10/31/2003 08:01:20