Multiple joins for user network

Databases
1

The following is query for user network. The problem is that I need one further step, to pull data from users for ids I get from network and here I am loosing how to do it.

users
userId | firstName | lastName …

knows
who | whom

-Getting user networking (2nd degree - friends of friends)


SELECT DISTINCT
k1.who AS who,
k2.who AS through1,
k2.whom AS whom
FROM knows AS k1 
LEFT JOIN knows AS k2 ON k1.whom=k2.who
WHERE k1.who = $userId

Now I am trying to make from this one further join, to get from users details for those ids. The following query is not ok but I don’t know how to fix it. Could somebody correct this query please or give me a tip what is wrong.


SELECT DISTINCT users.firstName, users.lastName, users.id  
FROM (users LEFT JOIN users ON users.id=k1.whom)
LEFT JOIN knows as k1
ON knows.whom=k1.who
WHERE knows.who= '$userId'

thanks so much!

2 
SELECT u1.userId    AS who_userId
     , u1.firstName AS who_firstName
     , u1.lastName  AS who_lastName 
     , u2.userId    AS whom_userId
     , u2.firstName AS whom_firstName
     , u2.lastName  AS whom_lastName 
  FROM users AS u1
LEFT OUTER
  JOIN knows AS k
    ON k.who = u1.userId
LEFT OUTER
  JOIN users AS u2
    ON u2.userId = k.whom
 WHERE u1.userId = $userId
3

Thanks. I tried also the below code 2 which works great. But the problem of both (yours and mine solution) is that I need only unique users. I tried this:


SELECT users.id, users.firstName, users.lastName, k2.whom
FROM users
RIGHT JOIN
(SELECT DISTINCT users.id

but in this way I get error Unknown column ‘k2.whom’ in ‘field list’ because I selected DISTINCT users.id. But I also need to show via who each person is related. So I need to pull from users information from whom and through1.

I think the best solution will be to create temporary table. Group by is too slow. Or is there any other solution?

And this is a code which is also solution to my previous problem:


$myNetwork=mysql_query("
SELECT DISTINCT
users.id, 
users.firstName, 
users.lastName,
k1.who AS who,
k2.who AS through1,
k2.whom AS whom
FROM (users INNER JOIN knows AS k2 ON users.id=k2.whom) 
LEFT JOIN knows AS k1 ON k1.whom=k2.who
WHERE k1.who=311
4

no, i’d be willing to bet that that is not the best solution

could you please show a few sample rows from each table, and then show what results you want from the query? i’m not sure i understand what you’re trying to do

5

I would like to get
who_userId
who_firstName
who_lastName
whom_userId
whom_firstName
whom_lastName

where whom_userId must be distinct. So the only difference is about distinct.

6

Please provide some sample rows from the table along with expected output.

note that distinct works across ALL fields selected in the SELECT clause.