4个表关联操作的sql语句怎么写？

Question

两张表关联查询这样写就可以体现出多个列值组合并用逗号隔开
四张表应该怎么写这个sql语句呢？

SELECT
    a.*,
    group_concat( b.fcabin_cpbh )
FROM
    cd_car AS a
    LEFT JOIN cd_fcabin AS b ON find_in_set( b.fcabin_id, a.fcabin_id ) 
GROUP BY
    a.car_id

SELECT
    a.*,
    b.fcabin_cpbh,
    c.fair_cpbh,
    d.foil_cpbh 
FROM
    cd_car AS a
    INNER JOIN cd_fcabin AS b
    INNER JOIN cd_fair AS c
    INNER JOIN cd_foil AS d ON a.fcabin_id = b.fcabin_id 
    AND a.fair_id = c.fair_id
    AND a.foil_id = d.foil_id

如果这样写的话，fcabin_cpbh就只有一个值了

cd_car表

car_id	car_name	fcabin_id	fair_id	foil_id
1	宝马	1,2	6,7	1,2

cd_fcabin 表

fcabin_id	fcabin_cpbh
1	KTBH001
2	KTBH002
3	KTBH003

cd_fair 表

fair_id	fair_cpbh
6	KQBH066
7	KQBH077
8	KQBH088

cd_foil 表

foil_id	foil_cpbh
1	JYBH001
2	JYBH002
3	JYBH003

最终想实现效果如下
请教 group_concat( ) 和 find_in_set( ) 一起用，4个表关联怎么写

car_id	car_name	fcabin_id	fair_id	foil_id	fcabin_hpbh	fair_hpbh	foil_hpbh
1	宝马	1,2	6,7	1,2	KTBH001,KTBH002	KQBH066,KQBH077	JYBH001,JYBH002

Answer 1

用子表连接查询。

SELECT group_concat( e.fcabin_cpbh ) from
(SELECT
    a.*,
    b.fcabin_cpbh,
    c.fair_cpbh,
    d.foil_cpbh 
FROM
    cd_car AS a
    INNER JOIN cd_fcabin AS b
    INNER JOIN cd_fair AS c
    INNER JOIN cd_foil AS d ON a.fcabin_id = b.fcabin_id 
    AND a.fair_id = c.fair_id
    AND a.foil_id = d.foil_id)  e