Need Help With 3 Table Join

[NoSalt]

Hello All

 

I need help with a 3 table join that is driving me crazy. First, I'll describe the tables:

 

Table 1 = users:

id

uuid

username

password

 

Table 2 = profiles:

id

uid

firstName

lastName

 

Table 3 = friends:

id

uid

friendid

 

 

The "uid" in profiles and friends are keys to the "id" in users, that is how I tie all of my tables together. When a user adds a friend, the users "id" gets stored in the "uid" field of the friends table and the "id" of the friend gets stored in the "friendid" field of the friends table. What I want is a query to give me the uuid,firstName,lastName of the friends when I run a query for a particular users "id"; for example, if I want all of the friends for "Joe".

 

If somebody could help with this I would greatly appreciate it.

 

Thank you for reading. 

[fenway]

First, your live would be SO much easier if your DB was like this:

 

Table 1 = users:

user_id

username

password

 

Table 2 = profiles:

profile_id

user_id

firstName

lastName

 

Table 3 = friends:

friend_id

user_id

other_friend_id

 

Then the JOIN itself should be easily constructed.

[Eiolon]

I would approach this by combining the USERS and PROFILES tables (unless there is some reason why you are having them separated).

 

TABLE 1 = Users

====================

user_id

username

password

firstName

lastName

 

TABLE 2 = Friends

====================

user_id

friend_id

 

The next thing to note in your query is to not query by someone's name.  You can have multiple "Joe's" in your database.  Exactly which "Joe" are you trying to query?  Query by the user_id instead.

 

SELECT users.user_id, firstName, lastName FROM users JOIN friends ON users.user_id = friends.friend_id WHERE friends.user_id = 1;