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:2778 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
