It is all good Jeff. Your logic worked as intended and unintended. =) So win, win!
On 3/16/07, Jeffry Houser <[EMAIL PROTECTED]> wrote: > > 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> > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2784 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
