how to create table which has a column from another table in mySQL

Question

i want to create a table :

products

which looks like (these are columns name)

Id Name Category-id Description

now Category-id column gets the values from another table

category

which looks like

Id Name Description

now category.Id is used in products.Category-id

how to do this in mySQL

Answer 1

It seems that catedory-id is the link between the two tables. In that case you may want to read about FOREIGN KEY. For example, see here

Answer 2

I think what you need is called a VIEW in SQL parlance. That is, a virtual table, created dynamically based on a SELECT statement. I would do it like this:

CREATE VIEW product_with_cat AS
SELECT p.Id, p.Name, c.Name as Category, c.Description as Category_desc
FROM products p INNER JOIN category c ON p.Category_id = c.Id;