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
The most significant release in over 10 years. Upgrade & see new features.
http://www.adobe.com/products/coldfusion?sdid=RVJR
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2781
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
