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:227685 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
