Complex(?) Query Frustration

sjarman · #1 (**permalink**) 04-26-11, 21:23

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

r937 · #2 (**permalink**) 04-26-11, 21:59

please explain how person 2 matches person 1 only once

what is the purpose of the name column?

sjarman · #3 (**permalink**) 04-26-11, 22:03

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.

r937 · #4 (**permalink**) 04-26-11, 22:21

Quote:

Originally Posted by sjarman

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

sjarman · #5 (**permalink**) 04-26-11, 22:35

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.

r937 · #6 (**permalink**) 04-26-11, 23:05

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)

sjarman · #7 (**permalink**) 04-26-11, 23:52

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

mike_bike_kite · #8 (**permalink**) 04-27-11, 04:26

Moved thread to MySQL forum

sjarman · #9 (**permalink**) 04-27-11, 22:17

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.

sjarman · #10 (**permalink**) 04-27-11, 22:22

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

r937 · #11 (**permalink**) 04-27-11, 22:26

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

sjarman · #12 (**permalink**) 04-27-11, 22:34

Sorry - the line breaks were missing because I copied this straight from my PHP code.

All sorted and working though.

Thanks!

Internet Services

Web Development

Digital Marketing

Consumer Tech