Using table values as column

Question

I have 3 tables:

1. Products, containing - id, name and description
2. Attributes, containing - id, name

3. prod_attri_rel, containing - id, prod_id, attri_id and value

   1. Products looks like:

 1 | test | just a description for test
 2 | test2| just another description

2. Attributes looks like:

 1 | height
 3 | length
 2 | width
 4 | power
 5 | id

3. prod_attri_rel looks like:

1 | 1 | 1 | 2
2 | 1 | 2 | 25
3 | 1 | 3 | 20
4 | 2 | 1 | 2
5 | 2 | 2 | 25
6 | 2 | 3 | 20
7 | 2 | 4 | UBEC
9 | 2 | 5 | BC2212-850

After looking at https://dba.stackexchange.com/a/33307/48727 and https://stackoverflow.com/a/695860/1820180

Now I would like to get this result:

id | name | height | width | length | maybe other attr | so on and so forth
  1| test | 2      | 25    | 20     | ...

When I use the following query:

SELECT p.id, p.name , par.value, a.name FROM products p 
JOIN prod_attri_rel par on p.id = par.prod_id
JOIN attributes a on par.attri_id = a.id

I get:

id | name | value| name
1  | test | 2    | height
1  | test | 25   | width
1  | test | 20   | length

Then when I tried:

SELECT p.id, p.name , group_concat( par.value ), group_concat( a.name ) FROM products p 
JOIN prod_attri_rel par on p.id = par.prod_id
JOIN attributes a on par.attri_id = a.id GROUP BY p.name

I get a better result but not really it:

id | name | value   | name
1  | test | 25,2,20 | width,height,length

Last I found this https://stackoverflow.com/a/10926106/1820180 but I did not understand it enough to adapt it to my problem.

Any help in this matter would be appreciated, even if it is finding another method, or getting it to work.

Purpose: This is a database for different parts to RC toys, such as motors, ESC's wheels, propellers and such, all have different attributes, but all motors have same attributes, all wheels have same attributes.

Answer 1

The output of your query is fine but you must then pivot the data using GROUP BY. (SQL Fiddle)

Query:

SELECT p.id, p.name 
  , MAX(IF(a.name='height',par.val,0)) As 'height'
  , MAX(IF(a.name='width',par.val,0)) As 'width'
  , MAX(IF(a.name='length',par.val,0)) As 'length'
FROM products p 
JOIN prod_attri_rel par on p.id = par.prod_id
JOIN attributes a on par.attri_id = a.id
GROUP BY p.id, p.name
;

Output:

id  name    height  length  width
1   test    2       20      25
2   test2   3       35      30

Data:

CREATE TABLE Products(id int, name varchar(50), info varchar(50));

INSERT INTO Products(id, name, info) VALUES
(1, 'test', 'just a description for test')
, (2, 'test2', 'just another description');

CREATE TABLE Attributes(id int, name varchar(50));
INSERT INTO Attributes(id,name) values
    (1, 'height')
    , (3, 'length')
    , (2, 'width')
    , (4, 'power')
    , (5, 'id');

CREATE TABLE prod_attri_rel(id int, prod_id int, attri_id int, val varchar(20));
INSERT INTO prod_attri_rel(id, prod_id, attri_id, val) values
    (1, 1, 1, '2')
    , (2, 1, 2, '25')
    , (3, 1, 3, '20')
    , (4, 2, 1, '3')
    , (5, 2, 2, '35')
    , (6, 2, 3, '30')
    , (7, 2, 4, 'UBEC')
    , (9, 2, 5, 'BC2212-850');

Answer 2

Michael Green sent me on track with the EAV pattern made it searchable now i had a name for it.

The solution for the problem is:

SELECT 
    a.entity AS id,
    a.value AS title,
    b.value AS description,
    c.value AS height,
    d.value AS width,
    e.value AS length
FROM
    attributes a
        JOIN
    attributes b USING (entity)
        JOIN
    attributes c USING (entity)
        JOIN
    attributes d USING (entity)
        JOIN
    attributes e USING (entity)
WHERE
    a.attribute = 'name'
        AND b.attribute = 'description'
        AND c.attribute = 'height'
        AND d.attribute = 'length'
        AND e.attribute = 'width'