User options in another table: what is the best practice to check if an option is there?

Question

I have several option that a user can have, mainly to validate his presence around the site.

Tables are like this:

Users:

id=1
username=stackoverflow
password=oSKAJMMS;
address=xyz
...

Options:

user_id=1
option=AD3

---

user_id=1
option=AC1

At some point I need to check if he has a particular option (like: AD3, AC1 etc) in the "options" table, minding that an user with no option simply doesn't exist in the "options" table.

What is the best practice for this? I though about a JOIN but every time I read stackoverflow I find out simpler and more effective methods, so I ask you what should I do!

Thank you

Answer 1

SELECT  *
FROM    users u
WHERE   EXISTS (
        SELECT  1
        FROM    options o
        WHERE   o.user_id = u.id
                AND o.option = 'AC1'
        )

Make sure you have an index on options (user_id, option)