T-SQL Picking up active IDs from a comma seperated IDs list
- by hammayo
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
