SQL Query, Join 4 tables, 2 group_concats + conditional, Help

Question

Hello, I created an sql query to return all the client data in a single query, this query works partially if all conditions are satisfied, but this system uses soft delete, where all records cannot be deleted and only marked as inactive. the status field is marked with Zero (0) for inactive and One (1) for active. the tables are:

tbl_cliente(id_cliente(pk),status,nome, etc...) 
tbl_endereco(id_endereco(pk),status,cliente_id(fk),logradouro, etc...) 
tbl_email(id_email(pk),status,cliente_email_id(fk),email, etc...)
tbl_telefone(id_telefone(pk),status,cliente_fone_id(fk), telefone, etc...) 

and the relationship is 1 for many: a customer has/may have multiple emails, phones, addresses

the query:

SELECT 
    C1.id_cliente,
    C1.nome,
    C1.fones,
    C2.emails,
    C2.endereco,
    C2.numero
FROM
    (SELECT 
        cliente.`status`,
            cliente.id_cliente,
            cliente.nome,
            GROUP_CONCAT(CONCAT_WS('|', telefone.telefone, telefone.tipo)) AS fones
    FROM
        cliente
    LEFT JOIN telefone ON telefone.cliente_fone_id = cliente.id_cliente
    WHERE
        telefone.status = 1
    GROUP BY cliente.id_cliente) AS C1
        INNER JOIN
    (SELECT 
        cliente.id_cliente,
            endereco.endereco,
            endereco.numero,
            GROUP_CONCAT(email.email) AS emails
    FROM
        cliente
    LEFT JOIN email ON email.cliente_email_id = cliente.id_cliente
    LEFT JOIN endereco ON endereco.cliente_id = cliente.id_cliente
    WHERE
        email.status = 1 AND endereco.status = 1
    GROUP BY cliente.id_cliente) C2 ON C1.id_cliente = C2.id_cliente
WHERE
    C1.status = 1;

i need the query to search the other records even if the status of one or more of the conditional ones is not satisfied. for example:

If the customer has no email or if the emails are marked as inactive the query does not back the other records from the other tables and that’s what I’m trying to do. I’m wearing the Mariadb.

Base dump

Answer 1

There was no missing parentheses?

SELECT 
    C1.id_cliente,
    C1.nome,
    C1.fones,
    C2.emails,
    C2.endereco,
    C2.numero
FROM
(
    (SELECT 
        cliente.`status`,
            cliente.id_cliente,
            cliente.nome,
            GROUP_CONCAT(CONCAT_WS('|', telefone.telefone, telefone.tipo)) AS fones
    FROM
        cliente
    LEFT JOIN telefone ON telefone.cliente_fone_id = cliente.id_cliente
    WHERE
        telefone.status = 1
    GROUP BY cliente.id_cliente) AS C1
    LEFT JOIN (SELECT 
        cliente.id_cliente,
            endereco.endereco,
            endereco.numero,
            GROUP_CONCAT(email.email) AS emails
    FROM
        cliente
    LEFT JOIN email ON email.cliente_email_id = cliente.id_cliente
    LEFT JOIN endereco ON endereco.cliente_id = cliente.id_cliente
    WHERE
        email.status = 1 AND endereco.status = 1
    GROUP BY cliente.id_cliente) C2 ON C1.id_cliente = C2.id_cliente
)
WHERE
    C1.status = 1;

Answer 2

I achieved the desired result by making a SELECT with WHERE and LEFT JOIN for each table so that each condition was respected and not nested as it was before. Grateful to all who have helped, I hope this answer will help someone else!

 
SELECT
    C0.id_cliente,C0.nome,C1.fones,C2.emails,C3.endereco,C3.numero
FROM(
(SELECT
    C.id_cliente,C.nome,C.`status`
    FROM cliente AS C
    WHERE C.`status` = 1) AS C0
    LEFT JOIN
(SELECT
    cliente.id_cliente,
    GROUP_CONCAT(CONCAT_WS('|', telefone.telefone, telefone.tipo)) AS fones
    FROM cliente
    LEFT JOIN telefone ON telefone.cliente_fone_id = cliente.id_cliente
    WHERE telefone.status = 1 GROUP BY cliente.id_cliente) 
    AS C1 ON C0.id_cliente = C1.id_cliente
    LEFT JOIN
(SELECT
    cliente.id_cliente,
    GROUP_CONCAT(email.email) AS emails
    FROM cliente
    LEFT JOIN email ON email.cliente_email_id = cliente.id_cliente
    WHERE email.status = 1 GROUP BY cliente.id_cliente) 
    AS C2 ON C0.id_cliente = C2.id_cliente
    LEFT JOIN
(SELECT cliente.id_cliente, endereco.endereco, endereco.numero
    FROM cliente
    LEFT JOIN endereco ON endereco.cliente_id = cliente.id_cliente
    WHERE endereco.status = 1 GROUP BY cliente.id_cliente) 
    AS C3 ON C0.id_cliente = C3.id_cliente
)
WHERE
    C0.status = 1 AND C0.id_cliente =1 ;