具有其他表的多个ID的SQL字段 [英] SQL field with multiple id's of other table

问题描述

有人可以给我一个如何创建此数据库结构的想法. 这是一个示例:

Could someone give me an idea how to create this database structure. Here is an example:

Table "countries":
id, countryname
1, "US"
2, "DE"
3, "FR"
4, "IT"

现在我还有另一个表产品"，并且我想在其中存储可以使用该产品的所有国家/地区:

Now I have another table "products" and in there I would like to store all countries where this product is available:

Table "products":
id,productname,countries
1,"product1",(1,2,4) // available in countries US, DE, IT.
2,"product2",(2,3,4) // available in countries DE, FR, IT.

我的问题: 如何设计产品"中的表格结构以能够存储多个国家/地区?

My question: How do I design the table structure in "products" to be able to store multiple countries?

我最好的想法是在其中放置一个逗号分隔的字符串(即"1,2,4")，然后拆分该字符串以查找每个条目.但是我怀疑这是最好的方法吗?

My best idea is to put a comma-separated string in there (i.e. "1,2,4"), then split that string to look up each entry. But I doubt that this the best way to do this?

谢谢大家的帮助，太神奇了！选择正确的答案很困难， 我最终选择了Gregs，因为他为我指出了JOIN的解释，并给出了如何使用它的示例.

Thank you all for your help, amazing! It was difficult to choose the right answer, I finally chose Gregs because he pointed me to a JOIN explanation and gave an example how to use it.

推荐答案

您需要为此然后您内部加入所有3个表格，以获取国家和地区"列表产品.

You then Inner Join all 3 tables to get your list of Countries & Products.

Select * From Country 
Inner Join CountryProduct On Country.CountryID = CountryProduct.CountryID 
Inner Join Product On CountryProduct.ProductID = Product.ProductID

这篇关于具有其他表的多个ID的SQL字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！