SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Location
    Boston
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need help with query to connect users with pics of same places

    I have 3 simple tables:

    places(id)
    users(id)
    pictures(place_id, user_id, url)

    Can someone help me get started on writing a query that shows the top 5 users who have posted pictures of the same places that a given user has posted?

  2. #2
    SitePoint Addict
    Join Date
    Sep 2007
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here you go :

    Code:
     SELECT users.id 
     FROM users 
               INNER JOIN pictures ON users.id = pictures.user_id 
               INNER JOIN places ON pictures.place_id = places.id     
     WHERE places.id = 1 
     LIMIT 0, 5

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Location
    Boston
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks but that's not quite what i had in mind i'm trying to find the top 5 users who have posted pictures for ALL the same places that a user has posted. it's a list of people who like the same places as a user.

  4. #4
    SitePoint Addict
    Join Date
    Sep 2007
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, here you go :

    Code:
     SELECT o_user_id, count(distinct o_place_id) 
     FROM  
          (SELECT p1.place_id AS o_place_id, p2.user_id AS o_user_id    
           FROM pictures p1 
                           INNER JOIN pictures p2 ON p1.place_id = p2.place_id 
           WHERE p1.user_id = 1) otable 
     GROUP BY o_user_id 
      HAVING o_user_id != 1 
      ORDER BY count(distinct o_place_id) DESC 
      LIMIT 0, 5;

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Location
    Boston
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wow. thanks. that works perfectly. i think i understand most of what's going on except otable - can someone explain what that's doing? thanks.

    (and for some reason "mysql otable" doesn't bring up anything on google and it's not in my mysql book)

  6. #6
    SitePoint Addict
    Join Date
    Sep 2007
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    otable is just a table alias for the derived table i.e. second select subquery.
    Last edited by dman_2007; Apr 21, 2008 at 04:30.


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
  •