mysql muliple从相同的列以不同的键联接 [英] mysql muliple joins from same colums with different keys

问题描述

我需要一些帮助，使其与下表进行第二次连接.以前我从这里得到了一些帮助，建议我添加第二个JOIN，但是这是我遇到的困难，需要一些帮助.

I need some help making the second join with the tables below. I got some help from here previously where it was suggested I need to add a second JOIN, however, this is where I'm stuck and need some assistance.

wp帖子

  -----------------
  id | Post_Title | 
  -----------------
  01 | Event 1    | 
  -----------------
  02 | Event 2    | 
  -----------------

wp-postmeta

-------------------------------------------------------
  meta_id  | post_id | meta_key          | meta_value |
  -----------------------------------------------------
  456      | 01      | _EventStartDate   | 01/01/2017 |
  -------------------------------------------------- ---
  789      | 01      | _EventEndDate     | 05/02/2017 |
  -----------------------------------------------------

我追求的最终结果是类似的

The end result I'm after is something like;

标题-从< _EventStartDate>开始，到< _EventEndDate>

Title - starts on <_EventStartDate > and ends on <_EventEndDate>

和即时消息使用以下命令获取数据:

and im using the following to get the data:

    $result = $wpdb->get_results ( "
SELECT $wpdb->posts.ID, $wpdb->posts.post_content, $wpdb->postmeta.meta_id, 

$wpdb->postmeta.post_id, $wpdb->postmeta.meta_key, $wpdb->postmeta.meta_value, $wpdb->posts.post_title 


FROM $wpdb->posts INNER JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id 

WHERE $wpdb->postmeta.meta_key = '_EventStartDate' 

ORDER BY $wpdb->postmeta.meta_value " );

现在，我被告知WHERE将只返回一行，并且我需要使用$wpdb->postmeta.meta_key = '_EventStartDate'进行第二次JOIN，但是在尝试实现此功能数小时后，我却无法获取和返回数据

Now I've been told that the WHERE will just return a single row and that I need to make the second JOIN using $wpdb->postmeta.meta_key = '_EventStartDate' but after hours of trying to implement this I'm unable to get and data back at all.

如果有人可以解决这个问题，那将是非常有帮助的，因为我想编写更多查询，我想我也将需要使用相同的主体.

If someone could help solve this it would be immensely helpful as I have a few more queries I would like to write and I'm guessing I will need to use the same principal with them too.

感谢阅读！

推荐答案

您可以使用单个内部联接来完成此任务，并按帖子ID分组，然后分割开始日期和结束日期:

You can do it with a single inner join, grouping by the post ID and then splitting start date and end date:

select 
wpposts.post_content,
substring_index(GROUP_CONCAT(meta_value order by str_to_date(meta_value,'%d/%m/%Y')), ',', 1) as start_date ,
substring_index(GROUP_CONCAT(meta_value order by str_to_date(meta_value,'%d/%m/%Y')), ',', -1) as end_date
from wpposts inner join wppostmeta
on wpposts.id = wppostmeta.post_id
where wppostmeta.meta_key='_EventStartDate' or wppostmeta.meta_key='_EventEndDate'
group by wppostmeta.post_id

这篇关于mysql muliple从相同的列以不同的键联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！