+1 to Jeff. This is the typical implementation of a many to many relationship.
On 3/16/07, Jeffry Houser <[EMAIL PROTECTED]> wrote: > > I don't fully understand the table structure. Are you making this > too complex? Why can't standard joins work? > > You have a table for color: ColorID, Color > > And you have a table for DayOfWeek: DOWID, DayOfWeek > > And you have a table with data: DataID, ColorID, DayOfWeekID, otherdata > > Just select like this: > > select DataID from dataTable > where colorID = myselectedColorID and DayOfWeekID = myselectedDayofweekID > > It'd be easy to modify this to use intersection tables if a "data" > can have multiple days of the week or multiple colors. > > At 01:08 PM 3/16/2007, you wrote: > >I've got a query that I build up based on user choices; part of the > >query, I want to have it return a short list of IDs based on said > choices. > > > >So for example let's say that the user chooses "color = 'red'" and "day > >of week = 'tuesday'". > > > >I need to pull out a list of the IDs where both values match. > > > >One trick is, data is stored in two different tables, tied to varying > >other IDs. Now, doing single queries to get these matches is cake, and > >then looping over those lists and figuring out what IDs are in both > >recordsets is cake. But I'm unsure as to the SQL syntax to do it within > >a single query. > > > >I'm basically looking for the opposite, or semi-opposite of UNION. > > > >I'm wondering if I need to go this route: > > > >select ID from table where ID IN ( > > select ID from table2 where (datamatch) > > and ID IN ( > > select ID from table3 where (other data match) > > ) > >) > > > >i.e. just do nesting all the way through. I'm going to give this a shot > >whilst those of you who are so inclined, mull this one over. :D > >--Scott > > > > -- > Jeffry Houser, Software Developer, Writer, Songwriter, Recording Engineer > AIM: Reboog711 | Phone: 1-203-379-0773 > -- > My Company: <http://www.dot-com-it.com> > My Podcast: <http://www.theflexshow.com> > My Blog: <http://www.jeffryhouser.com> > Connecticut Macromedia User Group: <http://www.ctmug.com> > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2779 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
