Welcome to WebmasterWorld Guest from

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.

userId | firstName | lastName ...

who | whom

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

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:

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

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