SQL difficult statement

Question

Currently I'm looking for a solution. English is not my native language so I added two pictures to clarify it a bit. My knowledge of SQL is rather basic.

Table Database

A chair (end-product) contains several components.
Each component contains an item number, each spare set contains multiple item numbers.
In the database a spare set is in the same table as an end-product.
The chairs and spare parts are categorized in different groups.

Is it possible to create a table like that? If it's possible how?

Thanks in advance!

Using one table might not be the best solve this. You will definitely need multiple tables which are related to each other. — luukvhoudt, Sep 12 '16 at 07:22
@jarlh, it's for an internship. I can two different tables, just not able to combine the two. — Jur, Sep 12 '16 at 07:24
@Fleuv, true, but it's en existing rather complex database so I can't change anything about that. — Jur, Sep 12 '16 at 07:25
So is this about creating the tables so they can be joined in a query to get that pivot result? Or do the tables already exist, and you want to know how to get that pivot result? — LukStorms, Sep 12 '16 at 08:35
If you want to do a general pivot (getting a column for every product, without knowing how many products there are) is not possible (and not advised, since you can end up with 10000 columns - who wants to read that?), otherwise check [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Solarflare, Sep 12 '16 at 09:03

score 1 · Answer 1 · answered Sep 12 '16 at 08:27

This would be my approach:

Table: Products  -- a table of products
ProductID  ProductName  DateAdded
1          Chair X      01/01/2016
2          Chair Y      05/05/2016
3          Spare Set A  06/06/2016
...

Table: Components  -- a table of components
ComponentID  ComponentName  ItemNumber  DateAdded
1            Backrest       X01         01/01/2016
2            Headrest       X02         01/01/2016
...

Table: ProductComponent  -- a lookup/mapping table to link Product and Component
PCID  ComponentID  ProductID  DateAdded
1     1            1          01/01/2016
2     2            1          01/01/2016
...

DateAdded is the date the product or component was first entered into the database. It is for audit purposes.

SQL difficult statement

1 Answers1