MySQL Multiple Join

Question

I have created the following query which pulls the data from 3 different tables:

    SELECT
    product_searchables_names.class_name AS Class_Name,
    product_searchables_names.label AS Attribute_Label,
    jss_products.extrafield102 AS Attribute1,
    jss_products.extrafield103 AS Attribute2,
    jss_products.extrafield104 AS Attribute3,
    jss_products.extrafield105 AS Attribute4,
    jss_products.extrafield106 AS Attribute5,
    jss_products.extrafield107 AS Attribute6
    FROM
    product_searchables_categories 
    JOIN product_searchables_names ON product_searchables_categories.searchableID = product_searchables_names.ID
    JOIN jss_products ON product_searchables_categories.categoryID = jss_products.categories
    WHERE
    jss_products.productID = '134890'
    ORDER BY
    product_searchables_categories.searchable_order ASC

Which outputs data in the following format

enter image description here

But I need it in this format, where row 1 picks the value from 'Attribute'1, row2 from 'Attribute2' etc:

How would i do this?

Thanks in Advance

Can you post the table structure and some sample data for each table? — Taryn, Jan 16 '13 at 14:50

Michael · Answer 1 · 2013-01-16T13:21:46.280

1

You could try something with the case statement http://dev.mysql.com/doc/refman/5.1/de/case-statement.html like

select
    product_searchables_names.class_name AS Class_Name,
    product_searchables_names.label AS Attribute_Label,
    CASE 
        WHEN product_searchables_names.label = 'Resolution' THEN jss_products.extrafield104
        WHEN product_searchables_names.label = 'Networked?' THEN jss_products.extrafield106
        WHEN ...
    END
from
    product_searchables_categories
...

or use class_name instead of label

edited Jan 16 '13 at 13:21

answered Jan 16 '13 at 13:15

Michael

1,922
1
19
17

hi, that would work for this particular product ID, but since there are over 200 different attribute labels, this wouldn't work database wide, thanks for your suggestion though! :) – Daniel Kilburn Jan 16 '13 at 13:28
but i guess, you will still need some kind of mapping between the class_name and the attribute fields... – Michael Jan 16 '13 at 13:34
i've managed to adapt your idea, each label had a unique id so I just did CASE WHEN product_searchables_id.label = '1' THEN jss_products.extrafield102 etc... – Daniel Kilburn Jan 16 '13 at 14:12
of course i would do, but i only have a repuation of 10, vote up needs 15! but thank you so much! – Daniel Kilburn Jan 16 '13 at 14:14

score 0 · Answer 2 · edited May 23 '17 at 10:34

0

What you are looking for is a pivot. I know how to do that in Oracle, but I don't believe there is an easy way to do it in mysql. This is often much easier to do in code after you get the result set than it is in SQL.

Here's a post describing how to do it using CASE, this is similar to the Oracle method.

mysql pivot query results with GROUP BY

edited May 23 '17 at 10:34

Community

1
1

answered Jan 16 '13 at 13:13

Peter Wooster

6,009
2
27
39

score 0 · Answer 3 · answered Jan 16 '13 at 14:47

Based on the images that you posted it appears that you need to unpivot the jss_products table. This process will take your values that are columns and convert it into rows.

So your query will be similar to this:

SELECT psn.class_name AS Class_Name,
    psn.label AS Attribute_Label,
    jp.Attribute
FROM product_searchables_categories psc
JOIN product_searchables_names psn
    ON psc.searchableID = psn.ID
JOIN
(
    select productID, categories, jp.extrafield102 as Attribute
    from jss_products jp
    union all
    select productID, categories, jp.extrafield103 as Attribute
    from jss_products jp
    union all
    select productID, categories, jp.extrafield104 as Attribute
    from jss_products jp
    union all
    select productID, categories, jp.extrafield105 as Attribute
    from jss_products jp
    union all
    select productID, categories, jp.extrafield106 as Attribute
    from jss_products jp
    union all
    select productID, categories, jp.extrafield107 as Attribute
    from jss_products jp
) jp    
    ON psc.categoryID = jp.categories
WHERE jp.productID = '134890'
ORDER BY psc.searchable_order ASC

MySQL Multiple Join

3 Answers3