Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a users table and friends table. The user with ID 1 has 4 friends (with IDs 2,3,4,13). I want to get names and surnames of users 2,3,4 and 13. I can to it with standard queries but it will slow down the process; querying for every friend. I didn't get how and what to use of left, joints, right joints, simple joints, ASs, ONs, ...

enter image description here

share|improve this question
2  
basic sql query questions are not encouraged here, please use stackoverflow. You have to post your current query/code here, to get optimization recommendation. –  Coder Hawk Apr 7 '11 at 11:30
1  
Do you want 2,3,4,13 or 2,3,4,14? I think you mistyped one or the other and just need to make them match. –  Leigh Riffel Apr 7 '11 at 18:27
    
@Sandy are these question just "not encouraged" or not rather actually off-topic? The FAQ is quite vague on what actually is and isn't on-topic... –  Jürgen A. Erhard Apr 12 '11 at 18:19

2 Answers 2

up vote 3 down vote accepted

If you want to get the names of the friends for a user perhaps try something like so:

SELECT u.firstname, u.surname 
FROM friend f, user u
WHERE (f.Person1 = 1 OR f.Person2 = 1)

This should get a back a list of names in your user table (providing that is what you have named your friends in the user table) that are friends with userID 1

I'm guessing your friend table contains the foreign key IDs of the friends making up the partnership?

share|improve this answer

I am assuming the friends table has a user_id field that corresponds to the id field in the users table.

Select name, surname from users
where id in 
(select user_id from friends where id=1)

This way, you can just plug the user id in the place where '1' is, and you will get all the respective friends. For example, if you want to find the name and surname of all the friends of the user id 5740 use:

Select name, surname from users
where id in 
(select user_id from friends where id=5740)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.