MySQL GROUP_CONCAT 和多个查找表

Question

我有七张 table 想要加入:

Table   programs
Column  id    program_name    program_description
        1     Self Help       Self Help Description...
        2     Wellness        Wellness Description...
        3     Education       Education Description...
______________________________________________________

Table   county
Column  id    county_name
        1     Stark
        2     Portage
        3     Wayne
_________________________

Table   services
Column  id    service_name    service_description
        1     Counseling      Counseling Description...
        2     Group Therapy   Group Therapy Description...
        3     Evaluation      Evaluation Description...
__________________________________________________________

Table   population
Column  id    population_name
        1     Adults
        2     Children
        3     Youth
_____________________________

Table   program_county
Column  id    program_id    county_id
        1     1             2
        2     1             3
        3     2             1
        4     2             2
_____________________________________

Table   program_service
Column  id    program_id    service_id
        1     1             2
        2     1             3
        3     2             1
        4     2             2
        5     2             3
______________________________________

Table   program_population
Column  id    program_id    population_id
        1     1             3
        2     2             1
        3     2             3
        4     3             1
_________________________________________

我正在尝试编写一个查询，该查询将为每个计划返回一行，并检索program_county、program_services 和program_population 表中的相关行，并查找这些服务的名称、人口和县，并将它们分别显示在一个字段中。喜欢:

id   program_name    program_description         Counties           Services                                 Population Served
1    Self Help       Self Help Description...    Portage, Wayne     Group Therapy, Evaluation                Youth
2    Wellness        Wellness Description        Stark, Portage     Counseling, Group Therapy, Evaluation    Adults, Youth

我知道我必须使用联接和 GROUP_CONCAT，但我承认我非常迷失。

Answer 1

如果您想要查看所有计划，无论它们是否与县、服务或人口相关联，您将需要使用外部联接。同样，您应该在要连接的值周围使用 coalesce() 来正确处理 NULL 值。

这样的事情应该有效:

select programs.id, programs.program_name, programs.program_description,
  group_concat(distinct coalesce(county.county_name,'')) as "Counties",
  group_concat(distinct coalesce(services.service_name,'')) as "Services",
  group_concat(distinct coalesce(population.population_name,'')) as "Population Served"
from programs 
  left outer join program_county on program_county.program_id = programs.id
  left outer join county on county.id = program_county.county_id
  left outer join program_service on program_service.program_id = programs.id
  left outer join services on services.id = program_service.service_id
  left outer join program_population on program_population.program_id = programs.id
  left outer join population on population.id = program_population.population_id
group by programs.id, programs.program_name, programs.program_description
order by programs.id