Regardless of whether you create that intersection table to fully normalize out color names, to do restrictions on both values there are two options:
Option A: Double joins with aliased table names. This does an INNER JOIN against the color you want, and a LEFT OUTER JOIN with a clause that the color record NOT exist for the color you don't want: SELECT * FROM users INNER JOIN user_colors user_colors_red ON user_colors_red.user_id = users.id AND user_colors_red.color = "red" LEFT OUTER JOIN user_colors user_colors_green ON user_colors_green.user_id = user.ids AND users_colors_red.color = "green" WHERE user_colors_green.user_id IS NULL Option B: Subqueries. More straightforward but depending on what DB you're using this may not be supported. The optimization/efficiency of subqueries varies quite widely between RDMBS. SELECT * FROM users WHERE EXISTS ( SELECT * FROM user_colors WHERE user_colors.user_id = users.id AND user_colors.color = 'red' ) AND NOT EXISTS ( SELECT * FROM user_colors WHERE user_colors.user_id = users.id AND user_colors.color = 'green' ) The main difference between the two is that Option A has the theoretic possibility of returning multiple rows *if* you have duplicate entries for "red" for a given user, whereas the second does not. On Mon, Aug 4, 2014 at 1:00 PM, Ben Wanicur <[email protected]> wrote: > Hey Glen > > I would suggest that you create a separate colors table that has a id, > name. Then create a users_colors table that has user_id, color_id. This > is called an intersection table. Then your would query something like this: > > Example: > SELECT * FROM users > INNER JOIN users_colors ON (users.id = users_colors.user_id) > INNER_JOIN colors ON (users_colors.color_id = colors.id) > WHERE colors.name = 'red'; > > Google a bit about intersection tables and joins if you are interested. > This approach allows you to add as many colors as you want without adding > columns to your users_colors table. > > Cheers > > Ben > > > On Mon, Aug 4, 2014 at 12:51 PM, Glenn Little <[email protected]> wrote: > >> Hope this sql-only (no ruby) question is okay. I have a table "users" >> which has user_id and name fields. >> >> I have a table "users_colors" which has user_id and color fields with a >> many-to-many relationship with users. >> >> For simplicity, say that the color field is either "red" or "green". A >> user can be in the color table no times, once ("red" or "green"), or twice >> (once "red", once "green".) >> >> I'm trying to come up with an sql query that will tell me all of the >> users that are "red" but are not "green". Not sure I can get what I want >> with a left outer join, but I'm not at all handy with the left join, so >> maybe I'm missing something. >> >> Any ideas? >> >> Thanks! >> >> -- >> -- >> SD Ruby mailing list >> [email protected] >> http://groups.google.com/group/sdruby >> --- >> You received this message because you are subscribed to the Google Groups >> "SD Ruby" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > -- > SD Ruby mailing list > [email protected] > http://groups.google.com/group/sdruby > --- > You received this message because you are subscribed to the Google Groups > "SD Ruby" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- -- SD Ruby mailing list [email protected] http://groups.google.com/group/sdruby --- You received this message because you are subscribed to the Google Groups "SD Ruby" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
