sorry I wasn't specific. OR won't work because it needs to match all the criteria.
There will be items that will match all of the sub clauses. I'm sure I'm not explaining clearly. I need the items that exist at the intersection of each clause; example (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) I want to get record 6 On 12/26/05, Ben Nadel <[EMAIL PROTECTED]> wrote: > > Replace your clause joins (AND) with ORs. Right now, you can never get > results cause there is no way that TypeIDFK can equal more than one value. > SQL will parse what you have without parens () since they can be > interchanged and will fail if any of the parts fail. > > Try: > > > Select * from typelookup > WHERE > (TypeIDFK = 1 AND Type IN (3,4)) OR > (TypeIDFK = 2 AND Type IN (1,5)) OR > (TypeIDFK = 3 AND Type IN (3,4)) OR > (TypeIDFK = 4 AND Type IN (1,4)) OR > (TypeIDFK = 5 AND Type IN (3,4)) > > ....................... > Ben Nadel > Web Developer > Nylon Technology > 6 West 14th Street > New York, NY 10011 > 212.691.1134 > 212.691.3477 fax > www.nylontechnology.com > > "Vote for Pedro" > > -----Original Message----- > From: John Wilker [mailto:[EMAIL PROTECTED] > Sent: Monday, December 26, 2005 4:32 PM > To: CF-Talk > Subject: Re: more of my SQL issues :) > > Hi All, > > I've continued this thread since my new question is about the same issue, > and about SQL code again. > > I've got this query > > Select * from typelookup > WHERE (TypeIDFK = 1 AND Type IN (3,4)) > AND (TypeIDFK = 2 AND Type IN (1,5)) > AND (TypeIDFK = 3 AND Type IN (3,4)) > AND (TypeIDFK = 4 AND Type IN (1,4)) > AND (TypeIDFK = 5 AND Type IN (3,4)) > > Which returns nothing, I'm not of the SQL syntax to accomplish what I > want. > Basically if I break down each of the (TypeIDFK = 5 AND Type IN (3,4)) > bits > into a seperate query i get a result set of several rows. There's at least > one item that meets each of the where clauses and is in each result set. > Is > there a way to combine all those into ( a working version) of the above > query? > > Thanks all > > J > > On 12/15/05, John Wilker <[EMAIL PROTECTED]> wrote: > > > > Ian and Deanna > > > > you guy's approach makes sense. Grouping should get me to the last > > stretch. > > > > Aaron, I'm sure a quick and dirty MSSQL function would work if I had any > > clue as to writing one :) > > > > But I think I can get where I need to be from the two queries provided, > > thanks all. > > > > J > > > > On 12/15/05, Aaron Rouse <[EMAIL PROTECTED]> wrote: > > > > > > I do this in Oracle with a function I wrote. I'd think you could do a > > > quick > > > and dirty function in MSSQL that builds the list to return based off a > > > query > > > to the database and looping over that and concatenating them > all. This > > > is > > > not the method I took in Oracle but seems like it would work. > > > > > > > > > On 12/15/05, John Wilker <[EMAIL PROTECTED]> wrote: > > > > > > > > no one? > > > > > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:227686 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
