Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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`)

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:

FROM user_hashes AS a
LEFT JOIN user_hashes AS b ON a.hash = b.hash
LEFT JOIN users on b.user_id =
WHERE a.user_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?

share|improve this question

Sure, just use a subquery

select distinct u.*
  user_hashes uh
  inner join users u on uh.user_id =
  uh.hash IN
    select hash
    from user_hashes buh
    where user_id = ?
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.