SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    multiple joins for user network

    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)
    Code:
    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.
    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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:
    Code:
    $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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by meee View Post
    I think the best solution will be to create temporary table.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please provide some sample rows from the table along with expected output.

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •