Welcome to WebmasterWorld Guest from 54.159.190.106

Forum Moderators: open

Message Too Old, No Replies

multiple joins for user network

   
3:24 pm on May 2, 2010 (gmt 0)

5+ Year Member



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!
6:53 pm on May 2, 2010 (gmt 0)

5+ Year Member



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?
 

Featured Threads

Hot Threads This Week

Hot Threads This Month