Having a relationship with oneself

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

Old Forums

CLOSED - General SQL Server

Having a relationship with oneself

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-31 : 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

15732 Posts

Posted - 2003-10-31 : 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