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.
