Geek Answers Handbook

How to find an identifier not in the master identifier table

Say I have a master table for Products , and another table contains lists of ProductIDs .

Table name: Products -------------------- ProductID, Title, Price 1, Title 1, 12.00 2, Title 2, 15.00 4, Title 4, 11.50 8, Title 8, 13.89 11, Title 11, 12.00 Table name: ListOfProducts -------------------------- SomeID, ProductIDs 34, 4,8,1 35, 8,10,2

Now you can see that in the ListOfProducts table, the entry with SomeID=35 , the list of products is 8,10,2 . How can I use SQL to quickly find an invalid ProductID 10 , since it is not in the main Products table?

My program is actually in classic ASP (deprecated), and the database is in MS SQL. I can do a loop in ASP to ListOfProducts over entries in ListOfProducts , but how can I use fast SQL to look for any invalid ProductID ? In this case, when the program loop for writing is 35 , the script should return ProductID 10 .

It looks very simple. But I just could not come up with a good solution. It can be done? Please, help!

Thanks.

0
source share
1 answer

You should not store identifiers as a comma-separated list, this is more like a many-to-many relationship. You should have a table like:

 SomeID | ProductID ------------------- 34 | 4 34 | 8 34 | 1 35 | 8 35 | 10 35 | 2

If you really have to use a comma separated list, you need to create some kind of split function

+1
source

More articles:


All Articles