JOIN three tables + GROUP_CONCAT when there's data + all the rest of the first table

Question

this might be hard... three tables

I would like to retrieve a list of starship data + crew (group concatenated) + the rest of the starship records without team.

this code is giving back starship with team member one per row...

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

this is sample data:

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

what i would like to have is just a "team" record with concatenated members, or null, when no team is on starship. see a sample table below:

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

I would use a correlated subquery for this:

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