Find Specific Rows

Question

I'm trying to build a rather specific query to find a set of user_ids based on topics they have registered to. Unfortunately it's not possible to refactor the tables so I have to go with what I've got.

Single table with user_id and registration_id I need to find all user_ids that have a registration_id of (4 OR 5) AND NOT 1

Each row is a single user_id/registration_id combination. My SQL skills aren't the best, so I'm really scratching my brain. Any help would be greatly appreciated.

Answer 1

Possibly not the best way to do it (my SQL skills aren't the best either), but should do the job:

SELECT user_id
FROM table AS t
WHERE registration_id IN (4, 5)
    AND NOT EXISTS (SELECT user_id
                    FROM table
                    WHERE user_id = t.user_id
                        AND registration_id = 1);

Answer 2

SELECT  *
FROM    (
        SELECT  DISTINCT user_id
        FROM    registrations
        ) ro
WHERE   user_id IN 
        (
        SELECT  user_id
        FROM    registrations ri
        WHERE   ri.registration_id IN (4, 5)
        )
        AND user_id NOT IN
        (
        SELECT  user_id
        FROM    registrations ri
        WHERE   ri.registration_id = 1
        )

Most probably, user_id, registration_id is a PRIMARY KEY in your table. If it's not, then create a composite index on (user_id, registration_id) for this to work fast.

Answer 3

Another way with eliminating duplicates of user_id:

SELECT  user_id
        FROM    registrations
        WHERE   registration_id IN (4, 5)
except
SELECT  user_id
        FROM    registrations
        WHERE   registration_id =1

One use of a table:

select user_id from registrations
where registration_id <=5
group by  user_id
having MIN(registration_id)>1 and MAX(registration_id)>= 4