首页 文章

两个表之间的多个连接 - Wordpress

提问于
浏览 次
1

我需要创建SQL来检索所有自定义帖子类型“item”及其所有值 . 这是表结构

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: 在此查询中，我在表之间 Build 了连接，以从表POSTS检索帖子（post_type 'item'）并将其与POSTMETA上的相关值相关联 . 我在每个项目的一列上从列meta_key / meta_value中分割POSTMETA的值 .

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

查询返回此结构：

|| *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

我尝试了内连接，左连接等没有成功 .
如果有人可以提供帮助我会很感激 .

mysql wordpress

1 回答

  • 0

    一种方法是将 posts 表连接到 postmeta 上的单独数据透视子查询 . 然后，再做两个连接到 posts 以引入您需要的信息 .

    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;

    演示

    回复于

相关问题