Replacing a PHP loop with a MySQL Query

Question

I have a MySQL database table where I store a user_id and a hash each time someone logs in. The hash is stored in a persistant cookie so it doesn't change as users log in and out of the site.

The table structure is:

CREATE TABLE `user_hashes` (
  `user_id` int(11) NOT NULL,
  `hash` varchar(32) NOT NULL,
  `hash_datetime` datetime NOT NULL,
  UNIQUE KEY `user_id` (`user_id`,`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The purpose of this is to allow me to track which user accounts are used by the same person, or at least from the same computer.

I need to write a query that accepts a user_id and finds all other user_id's with matching hashes.

So far my query is as follows:

SELECT DISTINCT users.*
FROM user_hashes AS a
LEFT JOIN user_hashes AS b ON a.hash = b.hash
LEFT JOIN users on b.user_id = users.id
WHERE a.user_id = ?
ORDER BY users.id

This works for all hashes matched to the user_id in the where clause but it is possible to also match against hashes related to the matched user_id's?

I could write a loop in PHP that keep running queries against the list of matched users until there are no more unique matches left but is there a nice SQL way to achieve the same result?

Answer 1

Sure, just use a subquery

select distinct u.*
from 
  user_hashes uh
  inner join users u on uh.user_id = u.id
WHERE 
  uh.hash IN
  (
    select hash
    from user_hashes buh
    where user_id = ?
  )