Join a table to itself? - 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

General SQL Server Forums

New to SQL Server Programming

Join a table to itself?

Author

Topic

Tones
Starting Member

17 Posts

Posted - 2010-08-17 : 09:46:22

I am still a beginner when it comes to SQL, but i am trying to get a table to join to itself, at least i think that is what i need. I shall try to explain!

eg, i have a table with a list of colors, and a column that will display its matching colors id.


ID - Color - Matching_id
1	Red	2
2	Blue	3
3	Green	4
4	Yellow	5
5	Black	6
6	White	1

But instead of showing the id in a results table, i would like to show the actual matching color, ie:


ID - Color -Matching_id
1	Red	Blue
2	Blue	Green
3	Green	Yellow
4	Yellow	Black
5	Black	White
6	White	Red

I have been trying to get the table to join itself but with incorrect results coming back each time. How do i get the matching color column to display the actualy name instead of the ID?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-17 : 09:50:03

[code]--Sample Data
declare @t table
(ID int,Color varchar(10),Matching_id int)
insert @t
select 1, 'Red', 2
union all select 2, 'Blue', 3
union all select 3, 'Green', 4
union all select 4, 'Yellow', 5
union all select 5, 'Black', 6
union all select 6, 'White', 1

--Query
select a.ID,a.Color,b.Color
from @t a
inner join @t b on a.Matching_id = b.ID

--Result
ID Color Color
----------- ---------- ----------
1 Red Blue
2 Blue Green
3 Green Yellow
4 Yellow Black
5 Black White
6 White Red[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources