Hey Glen Sorry I was confused by reading your question quickly. I think Ben H's answer is correct. Assuming the users_colors table has the following two columns: user_id, color. Regardless, when posting questions like this, I think it's always best to include the code to avoid any confusion.
Ben W On Mon, Aug 4, 2014 at 1:39 PM, Glenn Little <[email protected]> wrote: > Actually, this is a third-party database so I can't change the schema. > But really, by abstracting my question I may have obscured what I really > want to do. In fact, I don't actually even need a second table to > illustrate my question. > > I have a table with two columns: user, color (both of which could be > strings, or ids into another table, whatever). > > I'd like to find all users that have a "red" row but *not* a "green" row. > > I just saw Ben H's response come in... need to look at that further but > maybe using "EXISTS" and "NOT EXISTS" is useful even in the one-table > problem... Embarrassingly, I did not know about those (I use IN and NOT IN > frequently, but never those others)! > > Thanks again! > > -glenn > > > > 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.
