SQL查询以从附加联接获取相关值

Question

我有一个简单的Select语句，它从2个MySQL表返回数据，运行良好。我现在需要从第三个相关表返回一些数据，但不确定如何做到这一点。

这是我当前的SQL查询

 select
    p.ID,
    p.post_title,
    p.post_name,
    max(CASE WHEN pm.meta_key = '_thumbnail_id'  THEN pm.meta_value END ) as thumbnailID,
    CAST(max( CASE WHEN pm.meta_key = '_price'  THEN pm.meta_value END ) AS UNSIGNED) as Price,
    max( CASE WHEN pm.meta_key = '_stock_status'  THEN pm.meta_value END ) as stockStatus,
    max( CASE WHEN pm.meta_key = '_sku'  THEN pm.meta_value END ) as SKU,
    CAST(max( CASE WHEN pm.meta_key = '_sale_price'  THEN pm.meta_value END ) AS UNSIGNED) as salePrice,
    CAST(max( CASE WHEN pm.meta_key = '_regular_price'  THEN pm.meta_value END ) AS UNSIGNED) as regularPrice,
    CAST(max( CASE WHEN pm.meta_key = 'total_sales'  THEN pm.meta_value END ) AS UNSIGNED) as totalSales

 from wp_posts p 
 join wp_postmeta pm
 on p.ID = pm.post_id

 where post_type = 'product'
 group by p.ID

现在需要从wp_postmeta表中获取值，其中wp_postmeta表中的_thumbnail_id的值与wp_postmeta表中的post_id值匹配，并且meta_key值=_wp_attached_file。

下面是wp_postmeta表中的一些记录：

第一条记录的thumbnailid=100。我现在需要从wp_postmeta表（同一表）中获取记录的值，其中post_id=100，meta_key=_wp_attached_file，如下所示：

我知道thumbnail_id是作为thumbnailID在当前查询中返回的，只是不知道如何将其再次连接到wp_postmeta表。

Answer 1

这个查询最终成功了--谢谢您的帮助。

select
    p.ID,
    p.post_title,
    p.post_name,
    max(CASE WHEN pm.meta_key = '_thumbnail_id'  THEN pm.meta_value END ) as thumbnailID,
    CAST(max( CASE WHEN pm.meta_key = '_price'  THEN pm.meta_value END ) AS UNSIGNED) as Price,
    max( CASE WHEN pm.meta_key = '_stock_status'  THEN pm.meta_value END ) as stockStatus,
    max( CASE WHEN pm.meta_key = '_sku'  THEN pm.meta_value END ) as SKU,
    CAST(max( CASE WHEN pm.meta_key = '_sale_price'  THEN pm.meta_value END ) AS UNSIGNED) as salePrice,
    CAST(max( CASE WHEN pm.meta_key = '_regular_price'  THEN pm.meta_value END ) AS UNSIGNED) as regularPrice,
    CAST(max( CASE WHEN pm.meta_key = 'total_sales'  THEN pm.meta_value END ) AS UNSIGNED) as totalSales,

    max(CASE WHEN  meta2.meta_key = '_wp_attached_file' THEN meta2.meta_value END ) as thumbnailURL



from wp_posts p 
join wp_postmeta pm
on p.ID = pm.post_id

LEFT JOIN wp_postmeta meta2 
  ON meta2.post_id = pm.meta_value AND pm.meta_key = '_thumbnail_id'



where post_type = 'product'


group by p.ID

Answer 2

LEFT JOIN wp_postmeta AS meta2 
WHERE meta2.post_id = max(CASE WHEN pm.meta_key = '_thumbnail_id'  THEN pm.meta_value END )
AND meta2.meta_key = _wp_attached_file

但在我看来很难看。