How i can get the Data from a self referring Table

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 2005 Forums

Transact-SQL (2005)

How i can get the Data from a self referring Table

Author

Topic

MacJK
Starting Member

24 Posts

Posted - 2009-07-24 : 05:11:08

Hello,

how i can get the Data from a self referring Table? Can i make this with a View?

My Table:

Table_1
ID Name Item
Z A 1
Z B 2
Z C 3
Z D Y
X A 1
X B 2
Y F 2
Y G 5

Now i need to get a list like this:

SELECT * FROM Table_1 where ID ='Z'

ID Name Item
Z A 1
Z B 2
Z C 3
Z D Y
Z F 2
Z G 5

Many thanks for help and support.

best regrads,
Jaroslaw

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-24 : 05:18:40

What I can see is:
There is a table with data and there is a SELECT that works.
What I don't understand:
What is your problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

MacJK
Starting Member

24 Posts

Posted - 2009-07-24 : 05:23:21

Hello webfred,

sorry for this confiuse question i try to explain more:

The SELECT is a example like sould work.

Table_1
ID Name Item
Z A 1
Z B 2
Z C 3
Z D Y
X A 1
X B 2
Y F 2
Y G 5

SELECT * FROM Table_1 where ID ='Z'
ID Name Item
Z A 1
Z B 2
Z C 3
Z D Y
Z F 2
Z G 5

I want to add the Y IDs to the Result as Z because the Z ID is connected over Row 4 to Y.

How i can get the result like my examle?

br
Jaroslaw

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-24 : 05:23:25

[code]Z A 1
Z B 2
Z C 3
Z D Y<- here is the y
X A 1
X B 2
Y F 2<- this y going to become z
Y G 5<- this y going to become z[/code]

[code]Z A 1
Z B 2
Z C 3
Z D Y
Z F 2
Z G 5[/code]

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-24 : 05:37:16

[code]SELECT *
FROM (
SELECT ID, NAME, ITEM
FROM TABLE_1
UNION
SELECT 'Z', one.NAME, one.ITEM
FROM TABLE_1 one INNER JOIN TABLE_1 two on one.ID=two.item
WHERE two.ID = 'Z'
)a
WHERE ID = 'Z'[/code]
<- i know i cheat...but fun ^^

Hope can help...but advise to wait pros with confirmation...

MacJK
Starting Member

24 Posts

Posted - 2009-07-24 : 05:50:40

Best! Many thanks waterduck! Works.

br
Jaroslaw

MacJK
Starting Member

24 Posts

Posted - 2009-11-26 : 04:32:37

Hello, many thanks for Help and cheat now i have a extension to this.

I try some time to get it by myself but i don't work.

Now this have one ore more layers more.

Z A 1
Z B 2
Z C 3
Z D Y
X A 1
X B 2
Y F 2
Y G 5
2 K 1
2 K 3
3 C 8
3 H 9

The result should be like:

WHERE ID 'Z'

Z A 1
Z B 2
Z C 3
Z D Y
Y F 2
Y G 5
2 K 1
2 K 3

should i use a second UNION?

br
Jaroslaw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources