John, Maybe I cannot quite imagine how your DB is designed... but I am not sure how it is possible for all of those to return 6 if you AND'ing the TypeIDFK= parts. I am assuming this is an integer field in the DB table - how can they ever equal two different values?
....................... 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 5:15 PM To: CF-Talk Subject: Re: more of my SQL issues :) 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? > > > > > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227687 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
