Msg#: 4125749 posted 3:24 pm on May 2, 2010 (gmt 0)
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'
Msg#: 4125749 posted 6:53 pm on May 2, 2010 (gmt 0)
I found the solution:
$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
But the problem 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?