Take the 2-minute tour ×
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.

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?

share|improve this question
add comment

1 Answer

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 = ?
  )
share|improve this answer
add comment

Your Answer

 
discard

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.