2

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?

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 = ?
  )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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