Ain't nuthin wrong with old school :) I'll give both a try today.
On 12/29/05, Dave Francis <[EMAIL PROTECTED]> wrote: > > Not tested, and I know it's old-style, but wouldn't WHERE IN work here? > ie. something like > > SELECT * FROM typelookup > WHERE (TypeIDFK = 1 AND Type IN (3,4)) > AND WHERE Type IN > > (SELECT Type FROM typelookup > WHERE (TypeIDFK = 2 AND Type IN (1,5)) > AND WHERE Type IN > > (SELECT Type FROM typelookup > WHERE (TypeIDFK = 3 ... > > > > -----Original Message----- > From: John Wilker [mailto:[EMAIL PROTECTED] > Sent: Wednesday, December 28, 2005 4:39 PM > To: CF-Talk > Subject: Re: more of my SQL issues :) > > > Not too horrible no. there are five, for now :) It's not likely to grow > too > big. Was hoping for something a little more dynamic. I'd have to do a lot > of > list sorting to find which values are in each result set. I only want > things > that are in all N types. > > On 12/26/05, Sean Corfield <[EMAIL PROTECTED]> wrote: > > > > On 12/26/05, John Wilker <[EMAIL PROTECTED]> wrote: > > > (TypeIDFK = 1 AND Type IN (3,4)) (returns 6,12,15,16,17,77) > > > (TypeIDFK = 2 AND Type IN (1,5)) (returns 2,3,5,6,9,12,15,20,22) > > > (TypeIDFK = 3 AND Type IN (3,4)) (returns 6,33,66) > > > (TypeIDFK = 4 AND Type IN (1,4)) (returns 2,4,6,14,19,20,23,27) > > > (TypeIDFK = 5 AND Type IN (3,4)) (returns 3,6,7,8,10,12,33) > > > > If there are just five TypeIDFK values, you could do this as five > > consecutive queries. Would that be too horrible? > > > > SELECT * FROM typelookup > > WHERE (TypeIDFK = 1 AND Type IN (3,4)) > > > > SELECT * FROM typelookup > > WHERE (TypeIDFK = 2 AND Type IN (1,5)) > > AND theKey IN (resultFromQuery1)> > > SELECT * FROM typelookup > > WHERE (TypeIDFK = 3 AND Type IN (3,4)) > > AND theKey IN (resultFromQuery2) > > > > SELECT * FROM typelookup > > WHERE (TypeIDFK = 4 AND Type IN (1,4)) > > AND theKey IN (resultFromQuery3) > > > > SELECT * FROM typelookup > > WHERE (TypeIDFK = 5 AND Type IN (3,4)) > > AND theKey IN (resultFromQuery4) > > > > You'd construct resultFromQueryX as a list from a column of the result > > of query X. > > -- > > Sean A Corfield -- http://corfield.org/ > > Got frameworks? > > > > "If you're not annoying somebody, you're not really alive." > > -- Margaret Atwood > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227875 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
