T-SQL Picking up active IDs from a comma seperated IDs list

Question

I have two tables "Product" having following structure:

ProductID,ProductName, IsSaleTypeA, IsSaleTypeB, IsSaleTypeC
1, AAA, N, N, N
2, BBB, N, Y, N -- active
3, CCC, N, N, N
4, DDD, Y, N, N -- active
5, EEE, N, N, N
6, FFF, N, N, N
7, FFE, N, N, N
8, GGG, N, N, N
9, HHH, Y, N, N -- active

The second table "ProductAllowed" having following structure where ProductIDs is a comma separated string filed having mix of active and inactive product ids based on their IsSaleType mode.

ProductCode, ProductIDs
AMRLSPN, "1,2"
AMRLOFD, "1,3"
BLGHVF, "2,4,6"
BLGHVO, "2,4"
BLGHVD, "3,5"
BLGSDO, "0"
CHOHVF, "1,6"
CHOHVP, "1,2,7,8"
...
...

Q: Is there a t-sql query that will return a list of active records from the "ProductAllowed" table if any of three IsSaleType fileds is/are switched on for a product.

Based on the sample data the ProductAllowed records should return following records.
AMRLSPN
BLGHVF
BLGHVO
BLGSDO
CHOHVP

This needs to be applied in a SQLSERVER 2000 database containing aprox 150000 records.

Thanks

Answer 1

I can't test this, but it goes something like this...

SELECT PRODUCTCODE
FROM
    ProductAllowed PA
    JOIN
    (
    SELECT CAST(ID AS varchar(10)) AS ProductID
    FROM PRODUCT WHERE
    IsSaleTypeA = 'Y' OR 
    IsSaleTypeB = 'Y' OR
    IsSaleTypeC = 'Y'
    ) P ON
           PA.ProductIDs LIKE '"P.ProductID,%' OR 
           PA.ProductIDs LIKE '%,P.ProductID"' OR 
           PA.ProductIDs LIKE '%,P.ProductID,%'