SQL Query, join 4 tables, 2 group_concats + conditionals, Help

Question

SQL Query, join 4 tables, 2 group_concats + conditionals, Help

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Hello, I have created a query sql to return all client data in a single query, this query works partially if all conditionals are satisfied, but this system uses soft delete, in which all the records can not 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 client has / can have multiple emails, phones, addresses

a 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 fetch the remaining records even if the status of one or more of the conditionals is not satisfied. for example:

If the client does not have any email or if the emails are marked as inactive the query does not bring the other records from the other tables and that's what I'm trying to do. I'm using MariaDB.

Base dump

sql query mariadb

asked by anonymous 01.07.2015 / 14:27

2 answers

Queries data from the Federal Revenue website with PHP directive to put input fields in uppercase with angularJS

score 1 · Answer 1

Did you miss a parentage?

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;

score 0 · Answer 2

I got the desired result by doing a SELECT with WHERE and LEFT JOIN for each table so that each conditional was respected and not nested as it was before. Thanks to all who helped, I hope this answer helps 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 ;