I have 4 tables:

Users

record_id   first_name   last_name
1           John         Smith
2           Jim          Brown
3           Jane         Goodall

Polls

record_id   poll_question
1           What is your age?
2           What is your occupation?

Poll Options

record_id   poll_id  option_text
1           1        16-20
2           1        21-25
3           2        builder
4           2        technician

Poll Votes

record_id   user_id   poll_id   option_id
1           1         1         1
2           1         2         1
3           2         1         2
4           2         2         1

Given a specified user, how do I get all OTHER users who selected the same options for the polls answered by the specified user.

Ideally, it would provide a descending list of users according to how many questions were answered the same, i.e. users who voted all exactly the same would be at the top,开发者_C百科 down to users with no answers in common.

SELECT u.first_name, u.last_name, v.Answers
FROM Users AS u
LEFT JOIN (
    SELECT pv.user_id AS user, COUNT(*) AS Answers
    FROM PollVotes AS pv
    WHERE ((poll_id, option_id) IN
        (
            SELECT poll_id, option_id
            FROM PollVotes
            WHERE user_id = YOURUSER
        ))
    AND pv.user_id != YOURUSER
    GROUP BY pv.user_id
    ) AS v
ON u.record_id = v.user
WHERE u.record_id != YOURUSER
ORDER BY v.Answers DESC

The inner query selects all users with the same (poll_id, option_id) combination as the selected user. The rowcount per user is the number of common answers. The left join with the user table is to include users with no common answers in the result.

Here's another approach:

SELECT u1.record_id, u1.first_name, u1.last_name, u2.record_id comp_record_id, u2.first_name comp_first_name, u2.last_name comp_last_name, u1.options FROM (
    SELECT u.record_id, u.first_name, u.last_name, GROUP_CONCAT(pv.poll_id,'.', pv.option_id ORDER BY pv.poll_id, pv.option_id) options
    FROM users u
    INNER JOIN poll_votes pv ON pv.user_id = u.record_id
    GROUP BY u.record_id
) u1
INNER JOIN (
    SELECT u.record_id, u.first_name, u.last_name, GROUP_CONCAT(pv.poll_id,'.', pv.option_id ORDER BY pv.poll_id, pv.option_id) options
    FROM users u
    INNER JOIN poll_votes pv ON pv.user_id = u.record_id
    GROUP BY u.record_id
) u2 ON u1.options = u2.options AND u1.record_id <> u2.record_id 
WHERE u1.record_id = 1;

The two inner queries are identical, and could actually be turned into a view. The outer query simply joins the two on the question / answer lists to get the matches.