Multiple Join between two tables - Wordpress

Question

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.

Your current query looks completely correct to me. What is wrong with just adding new max of CASE expressions to cover the new columns you want to appear in your output? — Tim Biegeleisen
@TimBiegeleisen if I put only a new max case expressions it will return null. because I need to make other join(i tried with no succes) between two tables. — Darinif
It is not clear from your data what you are trying to do here. Consider simplifying your sample data to just one or two posts, and show exact input and output. If there is another table/join involved here, then show that also. — Tim Biegeleisen
@TimBiegeleisen, could you see the questions again. No, I think it's more clear. Thanks — Darinif

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2018-09-22T00:25:33

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;

Multiple Join between two tables - Wordpress

1 Answers

Demo