Jeff, You had a one to many from Color to a bridge table. You analogously had a DayOfWeek one to many to the same bridging table. Since you have one to many on both side of the bridge table, this create a many to many from Color to DayOfWeek.
You may not have implied this, but this is a common implementation of a way to create many to many relationships so you avoid redundant Colors and DaysOfWeek. On 3/16/07, Jeffry Houser <[EMAIL PROTECTED]> wrote: > > Actually, my description was of a one-to-many relationship. > > One piece of Data has only one color > But one color can have many pieces of data > > One piece of data per each Day of Week > But a day of week can have multiple pieces of data > > If you have a many to many relationship you want to set up > intersection tables. I've also heard them called linking > tables. This would add two tables to our example. One intersection > table will contain the data ID and the ColorID. The other would > contain the dataID and the DayOfWeekID. > > The select would be something like this: > select DataID > from dataTable join data_ColorTable on (dataTable.dataID = > data_ColorTable.dataID) > join data_DayOfWeekTable on ( dataTable.dataID = > data_DayOfWeekTable.dataID) > > where data_ColorTable.colorID = myselectedColorID and > data_DayOfWeekTable.DayOfWeekID = myselectedDayofweekID > > Things can get tricky when dealing with two intersection tables > like this, though. In the above example, you'll get no results if > the data does not have at least one color and one day of > week. Additionally you could be returning a lot of duplicate > data. If a data had 5 colors and 2 DayOfWeeks, you'd receive 10 > colors back (each one twice) and 10 DaysOfWeek back (each one 5 > times). That may not matter with such small amounts of data, but as > you add more tables and more data, it I something to keep aware of. > > I suspect there are ways to get the DayOfWeek from a date field in > the data table. But it really depends on your application. > > At 02:01 PM 3/16/2007, you wrote: > >+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> > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2782 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
