Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL field with multiple id's of other table

Tags:

sql

field

row

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?

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?

EDIT: 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.

like image 361
marimba Avatar asked Nov 09 '11 08:11

marimba


People also ask

Can a table have two IDs?

Yes, it is possible for a table to have more than one column which can uniquely identify a row of data. A column that can uniquely identify a record of data is known as a "Candidate Key" .

How do I select multiple values in SQL query?

The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.


1 Answers

You need an intersection table for that many-to-many relationship.

Table Country
CountryID, CountryName

Table CountryProduct
CountryID, ProductID

Table Product
ProductID, ProductName

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
like image 157
Greg Avatar answered Oct 09 '22 04:10

Greg