List:General Discussion« Previous MessageNext Message »
From:Russell Jones Date:November 24 2013 12:59am
Subject:Finding entries with similar values in different field of same table.
Any ideas?
View as plain text  
Thanks in advance. I have a table set up something like this... this is
greatly simplified to make the question easier to ask.

|id|hobby|person|enjoyment
--------------------------
| |soccer|john|10
| |soccer|jake|5
| |baseball|john|3
| |baseball|nate|5
| |baseball|jordan|2
| |tennis|john|3
| |tennis|nate|7
| |chess|john|10
| |chess|nate|3
| |swimming|casey|6
Each person has a score of how much they enjoy a particular hobby. If they
don't have a record in the database for a particular hobby, we assume they
don't participate.

Given 1 person, I need to know who at least participates in the most
similar hobbies. In the above example, if I chose John, it should return
Nate as (3) since they share 3 hobbies in common (baseball, tennis and
chess), jake and jordan would both get (1)

I could do this with multiple queries of course...

SELECT hobbies FROM tblname WHERE person='john';

SELECT person FROM tblname WHERE hobbies='baseball' AND hobbies='tennis'
AND hobbies='chess' AND name!='john'

and keep doing that but it seems INCREDIBLY inefficient.

I am certain there is a single optimized query to do this kind of stuff but
it is WAY beyond me. Any ideas?

Thread
Finding entries with similar values in different field of same table.Any ideas?Russell Jones24 Nov