Tutorial :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!


Solution: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)


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »