You are correct that the relationship between Color and DayOfWeek was many to many. I didn't catch that, but in my initial description "Data" would be the linking table.
In practical terms, generally people think about how any given data relates to its qualifiers, not how the qualifiers relate to each other. At 03:51 PM 3/16/2007, you wrote: >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> > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2783 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
