Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2010
    Posts
    9

    Unanswered: Complex(?) Query Frustration

    I'm totally stumped trying to create a query for a MySQL database. Hoping someone might have some hints for me.

    Given this example table, I need to be able to find the people (personid) that have the most matches with a provided personid based on two fields - type and name.

    For example, if I run the query for personid '1', it would need to return that personid 2 matches once, personid 3 matches twice, and personid 4 matches three times.

    In terms of return fields, personid and matchcount is all I need - e.g.

    Code:
    2,1
    3,2
    4,3
    Does anyone know if it's even possible?

    Thanks!


    Code:
    id	personid	type	name
    1	1	1	blue
    2	1	2	green
    3	1	1	yellow
    4	1	1	white
    5	2	2	red
    6	2	2	green
    7	2	3	blue
    8	2	1	purple
    9	3	1	purple
    10	3	1	white
    11	3	2	orange
    12	3	1	yellow
    13	3	2	white
    14	4	1	white
    15	4	3	grey
    16	4	2	green
    17	4	1	blue
    18	4	2	green

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please explain how person 2 matches person 1 only once

    what is the purpose of the name column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2010
    Posts
    9
    Person 2 matches on the 2,green combination and nothing else. i.e. persons 1 and 2 have "2,green" in common. Maybe "in common" is a better way to describe what I'm trying to query.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sjarman View Post
    Person 2 matches on the 2,green combination and nothing else. i.e. persons 1 and 2 have "2,green" in common.
    sorry, i still don't see how

    start with personid 1, and show how you make this "in common" connection by explaining which rows (using id column) implement it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2010
    Posts
    9
    Sorry - I'm probably explaining it poorly. This image should help:

    http://www.nocturnalware.biz/temp/matches.png

    You can see the "in common" items for person 2 and person 3, as related to person 1.

    So row id 2 (highlighted yellow) and row id 6 (highlighted yellow) are matches because both person 1 and person 2 share the "green" of type "2" combination.

    The green highlighted items show how person 1 and person 3 have 2 items in common.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i finally understand
    Code:
    SELECT that.personid
         , COUNT(*) AS matches
      FROM daTable AS this
    INNER
      JOIN daTable AS that
        ON that.type = this.type
       AND that.name = this.name
       AND that.personid <> this.personid 
     WHERE this.personid = 1
    GROUP
        BY that.personid
    note that the match count for 4 should be 4, not 3 (see rows 16 and 18)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2010
    Posts
    9
    Ooo... I'll get to work on that with some real data and see how it goes. Looks good though! Thanks so much!

    I've been staring at the problem so long now I don't think I ever would have got there.

    And yes, you're right about 4 - I stuffed up that count

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Moved thread to MySQL forum

  9. #9
    Join Date
    Jan 2010
    Posts
    9
    OK. So that all works perfectly, but I'm struggling with something again...

    I'm trying to now pull additional details from my profiles table at the same time. This is what I'm trying, but it's not working at all:

    Code:
    SELECT that.profileid, COUNT(that.*) AS matches, profiles.name, profiles.age FROM (likes AS this INNER JOIN profiles ON that.profileid = profiles.profileid) INNER JOIN likes AS that ON that.typeidx = this.typeidx AND that.data = this.data AND that.profileid <> this.profileid WHERE this.profileid = 1 GROUP BY that.profileid
    Can anyone spot what I'm doing wrong? My skills in this area are pretty limited unfortunately.

  10. #10
    Join Date
    Jan 2010
    Posts
    9
    And then of course, I immediately get it working. Sorry. This seems to be giving me the results I want:

    Code:
    SELECT that.profileid, COUNT(*) AS matches, profiles.name FROM likes AS this INNER JOIN (likes AS that INNER JOIN profiles ON that.profileid = profiles.profileid) ON that.typeidx = this.typeidx AND that.data = this.data AND that.profileid <> this.profileid WHERE this.profileid = 1 GROUP BY that.profileid

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    two things

    first, why would you remove the line breaks? a single line query is a ~real~ bitch to read
    Code:
    SELECT that.profileid
         , COUNT(that.*) AS matches
         , profiles.name
         , profiles.age 
      FROM (
           likes AS this 
    INNER 
      JOIN profiles 
        ON that.profileid = profiles.profileid  -- wrong join condition
           ) 
    INNER 
      JOIN likes AS that 
        ON that.typeidx = this.typeidx 
       AND that.data = this.data 
       AND that.profileid <> this.profileid 
     WHERE this.profileid = 1 
    GROUP 
        BY that.profileid
    second, why would you add parentheses in the FROM clause?

    also, the join inside the parentheses will cause a syntax error because the ON clause of that join refers to a table that isn't part of that join

    what you want to do is add the join to the profiles table as a subsequent join after the previous one, not in front of it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2010
    Posts
    9
    Sorry - the line breaks were missing because I copied this straight from my PHP code.

    All sorted and working though.

    Thanks!

Posting Permissions

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