当有数据+第一个表的所有剩余部分时，连接三个表+ GROUP_CONCAT

Question

这可能很难。三张表

​

​

我想检索一个星舰数据+船员(组连接)+没有团队的其他星舰记录的列表。

这段代码每行返回一个团队成员的星际飞船...

SELECT 
ss.starship_id, ss.starship_name, ss.starship_quota, ss.quota_insert_date as lastupd,
u.nome, u.cognome
FROM starship as ss
    LEFT JOIN user_props as up
    ON ss.starship_id=up.starship_id
    LEFT JOIN users as u
    ON u.id_user=up.id_user

以下是示例数据：

id  name        quota   name            surname
------------------------------------------------------
23  HAS CREW    7923    Luke            Skywalker
23  HAS CREW    7923    PAdme            Amidala
------------------------------------------------------
24  UnALTRA       0     Bilbo           Baggins
24  UnALTRA       0     Frodo           Baggins
------------------------------------------------------
22  NO CREW     3552    NULL             NULL


column "lastupd" have been omitted

我想要的只是一个包含连接成员的“团队”记录，或者当没有团队在星际飞船上时为空。请参见以下示例表：

id  name        quota   TEAM
------------------------------------------------------
23  HAS CREW    7923    Luke Skywalker, Padme Amidala
------------------------------------------------------
24  UnALTRA       0         BilBo Baggins, Frodo Baggins
------------------------------------------------------
22  NO CREW     3552    NULL

Answer 1

为此，我将使用相关子查询：

select s.*,
    (
        select group_concat(u.nome, ' ', u.cognome)
        from user_props up
        inner join users u on u.id_user = up.id_user
        where up.starship_id = s.starship_id
    ) team
from starship s