Database query question (sql server)

[TechBoyJK]

I'm trying to write a query to fetch some data. It's for a small script to display members that other members have 'saved' or deemed as favorites. The query is to select all of those favorites for a particular user.

A join is needed. The table that holds all of the favorites simply keeps the unique ID of the member, and the unique id of the member that is saved as a favorite. If a user has 20 favorites, there would be 20 records, each having the same 'member id', but different 'favorite member id's".

The first part of the query needs to select all records from the table 'member favorites' where 'member_id' = the member id of the person for whom we are displaying the favorites. So if i'm logged in and my member_id = 345 the query should look like

------------------------------
select *

FROM member_favorites

Where member_id = '345'
-----------------------------------

which might return say 5 records like

member_id = 345
favorite_member_id = 349

member_id = 345
favorite_member_id = 365

member_id = 345
favorite_member_id = 333

member_id = 345
favorite_member_id = 215

member_id = 345
favorite_member_id = 658

Now, that only gives me the unique ID for all of the members that have been saved. I still need to use all of those saved member id's to then query the core member table to get each member's username. But I'm not sure how to write the join to get both the ID's and the usernames. Can someone help me here?

The table structure:


(table) members

member_id
member_user_name


(table) member_favorites

member_id
favorite_member_id

 

[KLin]

You need to join to the members table twice

select MemFav.*, M1.UserName as MemberUserName, M2.UserName As FavoriteMemberUserName

FROM member_favorites MemFav INNER JOIN Members M1 On MemFav.Member_ID = M1.Member_ID INNER JOIN Members M2 On MemFav.Favorite_Member_ID = M2.Member_ID

Where MemFav.member_id = '345'

 

[TechBoyJK]

Originally posted by: KLin
You need to join to the members table twice

select MemFav.*, M1.UserName as MemberUserName, M2.UserName As FavoriteMemberUserName

FROM member_favorites MemFav INNER JOIN Members M1 On MemFav.Member_ID = M1.Member_ID INNER JOIN Members M2 On MemFav.Favorite_Member_ID = M2.Member_ID

Where MemFav.member_id = '345'

Thanks Klin. Is there any reason not to double join? Better method?

 

[KLin]

Originally posted by: TechBoyJK

Originally posted by: KLin
You need to join to the members table twice

select MemFav.*, M1.UserName as MemberUserName, M2.UserName As FavoriteMemberUserName

FROM member_favorites MemFav INNER JOIN Members M1 On MemFav.Member_ID = M1.Member_ID INNER JOIN Members M2 On MemFav.Favorite_Member_ID = M2.Member_ID

Where MemFav.member_id = '345'

Thanks Klin. Is there any reason not to double join? Better method?

What do you mean by double join?

 

[TechBoyJK]

Originally posted by: KLin

Originally posted by: TechBoyJK

Originally posted by: KLin
You need to join to the members table twice

select MemFav.*, M1.UserName as MemberUserName, M2.UserName As FavoriteMemberUserName

FROM member_favorites MemFav INNER JOIN Members M1 On MemFav.Member_ID = M1.Member_ID INNER JOIN Members M2 On MemFav.Favorite_Member_ID = M2.Member_ID

Where MemFav.member_id = '345'

Thanks Klin. Is there any reason not to double join? Better method?

What do you mean by double join?

when you say join the tables twice...

 

[KLin]

Originally posted by: TechBoyJK

Originally posted by: KLin

Originally posted by: TechBoyJK

Originally posted by: KLin
You need to join to the members table twice

select MemFav.*, M1.UserName as MemberUserName, M2.UserName As FavoriteMemberUserName

FROM member_favorites MemFav INNER JOIN Members M1 On MemFav.Member_ID = M1.Member_ID INNER JOIN Members M2 On MemFav.Favorite_Member_ID = M2.Member_ID

Where MemFav.member_id = '345'

Thanks Klin. Is there any reason not to double join? Better method?

What do you mean by double join?

when you say join the tables twice...

In the query I posted, I basically defined the member table twice as 2 separate instances of the same table so it could determine the username of the member_Id, and also the username of the favorite member id in the same query.