Locate records that meet ALL the requirements using IN or EXIST? Help?

Using SQL Server 2000...
I have a front end that provides the user with a 'search engine' to
pass search parameters. The stored procedure it calls joins multiple
tables/views to return the proper result.
I am running into a problem that I solved in the interface using code
(that takes WAY too long), but I know there must be a way to have SQL
Server do the work using T-SQL... as this seems a very simple issue.
The following is just a snippet of the pertinent information:
Assume I have a main Customer table with a unique CustID field.
I have another table of descriptive Flags with a unique FlagID field.
A third table Customer_Flag_Link has a unique ID field and contains 2
columns, the CustID and the FlagID.
Obvioulsy, the role of this 3rd table is to be able to assign multiple
Flags to each Customer.
Now assume I have the following data in the Customer_Flag_Link table:
UniqueID CustID FlagID
=============================== 1 123 333
2 123 444
3 123 222
4 987 444
5 987 222
6 567 111
7 567 222
My issue is that I want to be able to locate Customers who have ALL of
the passed Flags associated with them, I do not know how many Flags
will be passed (and there are other search parameters passed as well -
but this is the piece that is giving me trouble, though I am sure it
is simple!)
For example, I want to return CustID where exists FlagID 222 AND 444.
Based on the above data it should return CustID 123 and 987, but not
567.
Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
this does not give me the results I want.
Any ideas would be appreciated!
Thanks,
AKThis might help.
SELECT COUNT(distinct FlagID)
FROM Customer_Flag_Link
WHERE FlagID IN (222,444)
AND Customer = 'Ralph'
HAVING COUNT(distinct FlagID) = 2
The number of items in the IN clause is what is used in the HAVING
test. Of course this could be written as an EXISTS subquery,
correlated on Customer.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Jun 2007 10:50:45 -0700, aklein <abklein@.optonline.net>
wrote:
>Using SQL Server 2000...
>I have a front end that provides the user with a 'search engine' to
>pass search parameters. The stored procedure it calls joins multiple
>tables/views to return the proper result.
>I am running into a problem that I solved in the interface using code
>(that takes WAY too long), but I know there must be a way to have SQL
>Server do the work using T-SQL... as this seems a very simple issue.
>The following is just a snippet of the pertinent information:
>Assume I have a main Customer table with a unique CustID field.
>I have another table of descriptive Flags with a unique FlagID field.
>A third table Customer_Flag_Link has a unique ID field and contains 2
>columns, the CustID and the FlagID.
>Obvioulsy, the role of this 3rd table is to be able to assign multiple
>Flags to each Customer.
>Now assume I have the following data in the Customer_Flag_Link table:
>
>UniqueID CustID FlagID
>===============================>1 123 333
>2 123 444
>3 123 222
>4 987 444
>5 987 222
>6 567 111
>7 567 222
>My issue is that I want to be able to locate Customers who have ALL of
>the passed Flags associated with them, I do not know how many Flags
>will be passed (and there are other search parameters passed as well -
>but this is the piece that is giving me trouble, though I am sure it
>is simple!)
>For example, I want to return CustID where exists FlagID 222 AND 444.
>Based on the above data it should return CustID 123 and 987, but not
>567.
>Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
>this does not give me the results I want.
>Any ideas would be appreciated!
>Thanks,
>AK|||Hmmmm...
I have to see how to stick that idea into the larger procedure... but
it gives me a starting point.
Thanks for the idea!
AK