如何找到ID不在主ID表 [英] How to find ID not in the master ID table

问题描述

让我们说我有对于产品，然后另一个表主表这使名单 ProductIDs

Let's say I have a master table for Products, and another table which keeps 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  

现在，你可以看到，在表 ListOfProducts 与 SomeID = 35 备案，产品清单为 8,10,2 。如何使用SQL来快速找出无效 10的ProductID ，因为它不是在主表的产品？

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

我的程序实际上是经典的 ASP （传统），数据库是MS SQL。我可以做一个循环，ASP通过 ListOfProducts 中的记录进行迭代，但我怎么使用一个快速的SQL找到任何无效的的ProductID ？在这种情况下，当程序循环录制 35 ，该脚本应该返回 10的ProductID 。

My program is actually in classic ASP (legacy), and the database is in MS SQL. I can do a loop in ASP to iterate through the records in ListOfProducts, but how do I use a quick SQL to find any invalid ProductID? In this case, when the program loop to record 35, the script should return ProductID 10.

这看起来很简单。但我就是想不出很好的解决的。可以这样做？
请帮助！

This looks really simple. But I just couldn't think of a good solution. Can this be done? Please help!

感谢您。

推荐答案

您不应该的ID存储为一个逗号分隔的列表，这似乎更像是一个多一对多的关系。你应该有一个表所示：

You should not store IDs as a comma seperated list, this seems more like a many-to-many relation. You should have a table like:

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

如果你真的必须使用逗号分隔列表，那么你需要建立某种形式的分裂功能的

If you really must use a comma seperated list then you need to create some sort of split function

这篇关于如何找到ID不在主ID表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！