I have a form with checkboxes and I need to make a sql to collect all products (prodID) from table product who have same (OccID) as in table OccDetail.

First I use table Occasion to create the checkboxes with the OccName where each checkbox has it OccID. I click each checkbox and as I click it is saved in session and search for products who has this OccID and display them on the OnClick event.

1. Table
Occasion
----------
OccID
OccName

2. Table
Product
----------
ProID
ProName

3. Table
OccDetail
----------
OcDID
OccID
ProID

I need to know how I build the sql string to find all ProID in Product who has related ProID in OccDetail with the selected OccID.

Here is the sql I am using but its not working when more then 1 checkbox is selected :

SELECT Product.ProID, Product.ProName
FROM (Product INNER JOIN
OccDetail ON OccDetail.ProID = Product.ProID)
WHERE (OccDetail.OccID = 8) AND (OccDetail.OccID = 6)

Your where clause contradicts your join. Try OR instead of AND and see if that does what you want.

thank you for your reply georgev
the reason I am using AND and not OR is I want only products that has both 6 and 8 in OccDetail. I dont want all products that has either 6 or 8
Maybe I didnt explain this clear enough sorry

Ok, then can you explain what about thhe query is not working when more than 1 checkbox is selected. Can we see the SQL that does work (i.e. only one selected) as well please.

If I select only 1 checkbox I should get all products that has that OccID even if they have more OccID
and when I select more checkboxes I want to narrow the search and display products that has those OccID even if they have more OccID but restrict to have at least all OccID thats selected.

This works only for 1
SELECT Product.ProID, Product.ProName
FROM (Product INNER JOIN
OccDetail ON OccDetail.ProID = Product.ProID)
WHERE (OccDetail.OccID = 8)

I'm still not 100% what you want to show, but maybe this will give you the result.