When Glen said that "I have a table 'users_colors; which has user_id and color fields", I was not thinking that he had a users_colors.color column, since he said fields (plural). I would recommend posting your schema to clarify. But regardless, I did not fully answer your question, and Ben H is correct. The same concept would apply to using an intersection table (many to many relationship) or if you do have a users_colors.color column, then Ben H's examples should work as they are.
On Mon, Aug 4, 2014 at 1:19 PM, Ben Hughes <[email protected]> wrote: > 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. > -- -- 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.
