| Author |
Topic |
|
Atrophybrain
Starting Member
7 Posts |
 Posted - 2011-03-14 : 17:14:23
|
Hello,I've got somehting I want to do in a query but can't figure out how to do it, or even if it can be done.I have a table that has a list of people with three columns (that are important to this issue):ID_numberNameFriends_withThe friends_with column is the ID of another person in the same table, and what I want to do is have a query that shows the ID_number and name columns, and also has a colum that shows the name of the person that they are friends with.So on a table with data like this: The query will return something like this- Thank you! |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2011-03-15 : 00:54:29
|
| Did you made any attempt ? create table #Friends(ID_number int, Name varchar(50),Friends_with int)Insert into #FriendsSelect 1,'Joe',2 union allSelect 2,'Bob',3 union allSelect 3,'Sue',1 Select T1.Id_number, T1.Name, T2.Name from #Friends T1 inner join #Friends T2on T1.Friends_with = T2.ID_number |
 |
|
|
Atrophybrain
Starting Member
7 Posts |
Posted - 2011-03-15 : 11:24:24
|
| That worked, Thanks!I had made a few attempts, but as I am relatively new to SQL I coulnd't figure out how to make it work. I didn't think to join the table to itself! |
|
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2011-03-15 : 16:08:18
|
quote:
Originally posted by Atrophybrain
That worked, Thanks!I had made a few attempts, but as I am relatively new to SQL I coulnd't figure out how to make it work. I didn't think to join the table to itself!
You are welcome  Next time please do post what you have tried and we will help you in identifying where you are going wrong. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Atrophybrain
Starting Member
7 Posts |
Posted - 2011-03-18 : 15:53:56
|
quote:
Originally posted by pk_bohra
Did you made any attempt ? create table #Friends(ID_number int, Name varchar(50),Friends_with int)Insert into #FriendsSelect 1,'Joe',2 union allSelect 2,'Bob',3 union allSelect 3,'Sue',1 Select T1.Id_number, T1.Name, T2.Name from #Friends T1 inner join #Friends T2on T1.Friends_with = T2.ID_number
I know it's been a few days, but I just wanted to give an update on the status of this.While what I said earlier (that it worked) is true, that was only when I had two test records int the table. Once I started populating the table fully, I started getting wierd problems where I would run the query and get instead of one result per person with the correct name in the t2.name column, I would get 3-6 results per person with a different name in the t2.name column for each entry for that person.I have no idea why that would be, but there you go.What I ended up having to do was use a subquery in the join, looking something like this:Select T1.Id_number, T1.Name, T2.Name from #Friends T1 left join ( Select ID_number, Name From #friends) as T2 on T1.Friends_with = T2.ID_numberAgain, why that would work and the other query wouldn't I have no idea. I just thought y'all might want to know. |
|
|
|
|
Data from a different column in a different record