CODEWITHSUNDEEP
mysqljoin
Daniel Kilburn
Daniel Kilburn

Reputation: 177

MySQL Multiple Join

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:

http://i50.tinypic.com/308bbcg.jpg

How would i do this?

Thanks in Advance

Upvotes: 1

Views: 86

Answers (3)

Taryn
Taryn

Reputation: 247690

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

Upvotes: 0

Michael
Michael

Reputation: 1952

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

Upvotes: 1

Peter Wooster
Peter Wooster

Reputation: 6089

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

Upvotes: 0

Related Questions