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
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227873
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
