mysql - mysql中的右外连接问题

Question

我编写了一个查询来从四个不同的表中获取数据，如下所示:

 SELECT  crm_countries.country_name as Country,crm_support_inquiry.event_name as Event, crm_inquiry_perticipant.company as Company,crm_inquiry_perticipant.contact_name ContactPerson,crm_inquiry_perticipant.email, 

GROUP_CONCAT(CONVERT(Expogroup, CHAR(200)) SEPARATOR '/') AS Expogroup,GROUP_CONCAT(CONVERT(Abdas, CHAR(200)) SEPARATOR '/') AS Abdas, GROUP_CONCAT(CONVERT(AfricaDetails, CHAR(200)) SEPARATOR '/') AS AfricaDetails, GROUP_CONCAT(CONVERT(Kenyadetails, CHAR(200)) SEPARATOR '/') AS Kenyadetails,GROUP_CONCAT(CONVERT(Findexporters, CHAR(200)) SEPARATOR '/') AS Findexporters,GROUP_CONCAT(CONVERT(Dubaiexporters, CHAR(200)) SEPARATOR '/') AS Dubaiexporters,GROUP_CONCAT(CONVERT(IndiaExportNews, CHAR(200)) SEPARATOR '/') AS IndiaExportNews FROM ( SELECT inquiry_id,event_id,event_name,CASE WHEN mailer_id = 1 THEN CONCAT(Edition,' - ',sent_on) END AS Expogroup,CASE WHEN mailer_id = 2 THEN CONCAT(Edition,' - ',sent_on) END AS Abdas,CASE WHEN mailer_id = 3 THEN CONCAT(Edition,' - ',sent_on) END AS AfricaDetails,CASE WHEN mailer_id = 4 THEN CONCAT(Edition,' - ',sent_on) END AS Kenyadetails,CASE WHEN mailer_id = 5 THEN CONCAT(Edition,' - ',sent_on) END AS Findexporters, CASE WHEN mailer_id = 6 THEN CONCAT(Edition,' - ',sent_on) END AS Dubaiexporters ,CASE WHEN mailer_id = 7 THEN CONCAT(Edition,' - ',sent_on) END AS IndiaExportNews FROM crm_support_inquiry )

 AS crm_support_inquiry, crm_inquiry_perticipant,  crm_countries where  crm_inquiry_perticipant.inquiry_id=crm_support_inquiry.inquiry_id and  crm_countries.country_id=crm_inquiry_perticipant.country GROUP BY crm_support_inquiry.inquiry_id, crm_support_inquiry.event_id,crm_support_inquiry.event_name

这给出了 crm_perticipant_inquiry 和 crm_support_inquiry 上相等的 id 数据。

现在我想要 crm_perticipant_inquiry 中所有 id 的数据，即使这些 id 的数据不存在于 crm_support_inquiry 中，它也必须返回 null。我使用了右外连接并按如下方式更改了查询，但我没有获得所需的输出。请帮忙!!

 SELECT  crm_countries.country_name as Country,crm_support_inquiry.event_name as Event, crm_inquiry_perticipant.company as Company,crm_inquiry_perticipant.contact_name ContactPerson,crm_inquiry_perticipant.email, 

GROUP_CONCAT(CONVERT(Expogroup, CHAR(200)) SEPARATOR '/') AS Expogroup,GROUP_CONCAT(CONVERT(Abdas, CHAR(200)) SEPARATOR '/') AS Abdas, GROUP_CONCAT(CONVERT(AfricaDetails, CHAR(200)) SEPARATOR '/') AS AfricaDetails, GROUP_CONCAT(CONVERT(Kenyadetails, CHAR(200)) SEPARATOR '/') AS Kenyadetails,GROUP_CONCAT(CONVERT(Findexporters, CHAR(200)) SEPARATOR '/') AS Findexporters,GROUP_CONCAT(CONVERT(Dubaiexporters, CHAR(200)) SEPARATOR '/') AS Dubaiexporters,GROUP_CONCAT(CONVERT(IndiaExportNews, CHAR(200)) SEPARATOR '/') AS IndiaExportNews FROM

 ( SELECT inquiry_id,event_id,event_name,CASE WHEN mailer_id = 1 THEN CONCAT(Edition,' - ',sent_on) END AS Expogroup,CASE WHEN mailer_id = 2 THEN CONCAT(Edition,' - ',sent_on) END AS Abdas,CASE WHEN mailer_id = 3 THEN CONCAT(Edition,' - ',sent_on) END AS AfricaDetails,CASE WHEN mailer_id = 4 THEN CONCAT(Edition,' - ',sent_on) END AS Kenyadetails,CASE WHEN mailer_id = 5 THEN CONCAT(Edition,' - ',sent_on) END AS Findexporters, CASE WHEN mailer_id = 6 THEN CONCAT(Edition,' - ',sent_on) END AS Dubaiexporters ,CASE WHEN mailer_id = 7 THEN CONCAT(Edition,' - ',sent_on) END AS IndiaExportNews FROM crm_support_inquiry )
 AS crm_support_inquiry right outer join crm_inquiry_perticipant on crm_inquiry_perticipant.inquiry_id=crm_support_inquiry.inquiry_id,  crm_countries where crm_countries.country_id=crm_inquiry_perticipant.country GROUP BY crm_support_inquiry.inquiry_id, crm_support_inquiry.event_id,crm_support_inquiry.event_name

Answer 1

尝试从查询中删除 crm_countries 表，并查看是否获得了 crm_perticipant_inquiry 中的所有 ID。您可以稍后添加 crm_countries 表。例如尝试这个

SELECT  crm_support_inquiry.event_name as Event, 
crm_inquiry_perticipant.company as Company,
crm_inquiry_perticipant.contact_name ContactPerson,
crm_inquiry_perticipant.email, 
GROUP_CONCAT(CONVERT(Expogroup, CHAR(200)) SEPARATOR '/') AS Expogroup,GROUP_CONCAT(CONVERT(Abdas, CHAR(200)) SEPARATOR '/') AS Abdas, GROUP_CONCAT(CONVERT(AfricaDetails, CHAR(200)) SEPARATOR '/') AS AfricaDetails, GROUP_CONCAT(CONVERT(Kenyadetails, CHAR(200)) SEPARATOR '/') AS Kenyadetails,GROUP_CONCAT(CONVERT(Findexporters, CHAR(200)) SEPARATOR '/') AS Findexporters,GROUP_CONCAT(CONVERT(Dubaiexporters, CHAR(200)) SEPARATOR '/') AS Dubaiexporters,GROUP_CONCAT(CONVERT(IndiaExportNews, CHAR(200)) SEPARATOR '/') AS IndiaExportNews FROM

 ( SELECT inquiry_id,event_id,event_name,CASE WHEN mailer_id = 1 THEN CONCAT(Edition,' - ',sent_on) END AS Expogroup,CASE WHEN mailer_id = 2 THEN CONCAT(Edition,' - ',sent_on) END AS Abdas,CASE WHEN mailer_id = 3 THEN CONCAT(Edition,' - ',sent_on) END AS AfricaDetails,CASE WHEN mailer_id = 4 THEN CONCAT(Edition,' - ',sent_on) END AS Kenyadetails,CASE WHEN mailer_id = 5 THEN CONCAT(Edition,' - ',sent_on) END AS Findexporters, CASE WHEN mailer_id = 6 THEN CONCAT(Edition,' - ',sent_on) END AS Dubaiexporters ,CASE WHEN mailer_id = 7 THEN CONCAT(Edition,' - ',sent_on) END AS IndiaExportNews FROM crm_support_inquiry )
 AS crm_support_inquiry 
    right outer join crm_inquiry_perticipant on crm_inquiry_perticipant.inquiry_id=crm_support_inquiry.inquiry_id 
GROUP BY crm_support_inquiry.inquiry_id, 
crm_support_inquiry.event_id,
crm_support_inquiry.event_name;