StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You cannot create nested table. And the thing on your mind is not a good idea to design table like that. You should have two tables (<em>exactly three which holds the description if the category</em>). One is for the <code>product</code> and the second table holds the <em>category for each product</em>. Example design would look like this,</p> <pre><code>CREATE TABLE Product ( ProductID INT PRIMARY KEY, ProductName VARCHAR(50) UNIQUE ); CREATE TABLE Category ( CategoryID INT PRIMARY KEY, CategoryName VARCHAR(50) UNIQUE ); CREATE TABLE Product_Category ( RecordD INT AUTO_INCREMENT PRIMARY KEY, CategoryID INT, ProductID INT, CONSTRAINT tb_uq UNIQUE(CategoryID, ProductID) ); </code></pre> <p>and Populate Sample Records</p> <pre><code>INSERT Category VALUES (1, 'Fruit'); INSERT Category VALUES (2, 'Vegetable'); INSERT Product VALUES (1, 'Apple'); INSERT Product VALUES (2, 'Banana'); INSERT Product VALUES (3, 'Cabbage'); INSERT Product VALUES (4, 'Squash'); INSERT Product VALUES (5, 'Tomato'); INSERT Product_Category (CategoryID, ProductID) VALUES (1,1); INSERT Product_Category (CategoryID, ProductID) VALUES (1,2); INSERT Product_Category (CategoryID, ProductID) VALUES (2,3); INSERT Product_Category (CategoryID, ProductID) VALUES (2,4); INSERT Product_Category (CategoryID, ProductID) VALUES (1,5); INSERT Product_Category (CategoryID, ProductID) VALUES (2,5); </code></pre> <ul> <li><a href="http://sqlfiddle.com/#!2/fd0e2/3" rel="noreferrer">SQLFiddle Demo (<em>with records included</em>)</a></li> </ul> <p>sample queries</p> <pre><code>-- NORMAL QUERY SELECT a.ProductName, c.CategoryName FROM Product a INNER JOIN Product_category b ON a.ProductID = b.ProductID INNER JOIN Category c ON b.CategoryID = c.CategoryID ORDER BY ProductName; -- If you want catgoryName to be comma separated SELECT a.ProductName, GROUP_CONCAT(c.CategoryName) CategoryList FROM Product a INNER JOIN Product_category b ON a.ProductID = b.ProductID INNER JOIN Category c ON b.CategoryID = c.CategoryID GROUP BY ProductName ORDER BY ProductName; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload