CODEWITHSUNDEEP
mysqlwordpress
Darinif
Darinif

Reputation: 29

Multiple Join between two tables - Wordpress

I need to create SQL to retrieve all custom post type "item" and all values of it. This is the table structure

POSTS table

|| *id*|| *post_title*                  || *post_name*       || *post_type*
|| 550 || Brand Test 04                 || brand-test-04     || brands 
|| 579 || 200 ml / 6.8 oz               || 200-ml-6-8-oz     || sizes 
|| 758 || Item recor 8doem recordado 88 || item-recordado-88 || item

POSTMETA table:

|| *post_id*    || *meta_key*       || *meta_value*
|| 758          || basicName        || Item recor 8doem recordado 88
|| 758          || basicBrandName   || 550
|| 758          || basicSize        || 579

My current query is: On this query I made a join between the tables to retrieve the post(post_type 'item') from table POSTS and related it to related values on POSTMETA. I split the values of POSTMETA from column meta_key/meta_value on one column for each item.

SELECT
    p.ID,
    p.post_title,
    p.post_name,
    MAX(case when pm.meta_key = 'basicBrandName' then pm.meta_value end) as idBrand,
    MAX(case when pm.meta_key = 'basicSize' then pm.meta_value end) as idSize
from xyz_posts p
inner join xyz_postmeta pm
    on p.ID = pm.post_id
where
    p.post_type = 'item'    and
    p.post_status = 'publish'

group by p.ID

The query return this structure:

|| *ID*|| *post_title*            || *post_name*             || *idBrand*|| *idSize*
|| 758 || Item recordado 88 Item  || item-recordado-88       || 550      || 579

I need to adjust the query to return this structure:

|| *id*  || *post_title*            || *post_name*          || *brand_title* || *brand_name*   || *size_title*     || *size_name*
|| 758   || Item recordado 88 Item  || item-recordado-88    || Brand Test 04 || brand-test-04  || 200 ml / 6.8 oz  || 200-ml-6-8-oz

I tried inner join, left join and etc with no success.
If someone could help I'll appreciate it.

Upvotes: 1

Views: 742

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522396

One approach is to join the posts table to a separate pivot subquery on postmeta. Then, do two more joins to posts to bring in the information you need.

SELECT
    p1.id,
    p1.post_title,
    p1.post_name,
    COALESCE(p2.post_title, 'NA') AS brand_title,
    COALESCE(p2.post_name, 'NA') AS brand_name,
    COALESCE(p3.post_title, 'NA') AS size_title,
    COALESCE(p3.post_name, 'NA') AS size_name
FROM posts p1
LEFT JOIN
(
    SELECT
        post_id,
        MAX(CASE WHEN meta_key = 'basicBrandName' THEN meta_value END) AS idBrand,
        MAX(CASE WHEN meta_key = 'basicSize' THEN meta_value END) AS idSize
    FROM postmeta
    GROUP BY post_id
) pm
    ON p1.id = pm.post_id
LEFT JOIN posts p2
    ON pm.idBrand = p2.id
LEFT JOIN posts p3
    ON pm.idSize = p3.id
WHERE p1.id = 758;

Demo

Upvotes: 0

Related Questions