Help with optimizing query

if I have the following tables:

Table Customer_tbl

cust_id as int (PK), ...

Table Item_tbl

item_id as int (PK), ...

Table Selected_Items_tbl

selected_item_id as int (PK), cust_id as int (FK), item_id as int (FK), ...

-

With the following query:

select cust_ID from selected_items_tbl WHERE item_id in (1, 2, n) GROUP BY cust_id, item_id HAVING cust_id in (select cust_id from selected_items_tbl where item_id = 1) AND cust_id in (select cust_id from selected_items_tbl where item_id = 2) AND cust_id in (select cust_id from selected_items_tbl where item_id = n)

-

Each of these tables has other items included. Selected_Items_tbl holds zero to many of the items from the item_tbl for each customer. If I am searching for a customer who has item 1 AND item 2 AND item n, what would be the most efficient query for this? Currently, the above query is what I am working with. However, it seems that we should be able to do this type of search in a single query (without subqueries).

E:

Maybe something like:

select cust_id
from ( select cust_id,
count (distinct item_id) as itemCount
from selected_items_tbl
where item_id in (1,2,n)
group by cust_id
having count (distinct item_id) = 3
) x

|||You shoul try this here:

select cust_ID

from selected_items_tbl tbl

WHERE EXISTS

(

select cust_id

from selected_items_tbl t1

where item_id = 1 AND t1.cust_id = tbl.cust_id

)

AND EXISTS

(

select cust_id

from selected_items_tbl t1

where item_id = 1 AND t1.cust_id = tbl.cust_id

)

AND EXISTS

(

select cust_id

from selected_items_tbl t1

where item_id = 1 AND t1.cust_id = tbl.cust_id

)

Group by cust_ID

Let me know if that helped you.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de