homepage Welcome to WebmasterWorld Guest from 54.166.65.9
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
multiple joins for user network
meee




msg:4125751
 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'


thanks so much!

 

meee




msg:4125801
 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?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved