Get records of 3 tables with JOIN

Navigation
1

I need to get data from 3 tables in the same SELECT. I need only get the user data from the users table, get the amount of quotas (column quantity_cotas) that the user has from the quotas table (if you have registration in that user table) and the amount of invoices the user has in the invoices table (if you have one too). I tried to do this: 

SELECT u.login, SUM( c.quantidade ) AS qtd , SUM( COALESCE( f.status, 1, 0 ) ) AS qtdF
FROM usuarios AS u
LEFT JOIN cotas AS c ON c.id_user = u.id
LEFT JOIN faturas AS f ON f.id_user = u.id

But it returns me twice the amount in the qtd and qtdF columns.

It was for me to return type:

  

alissonacioli | 14 | 2

And he's coming back to me

  

alissonacioli | 28 | 4

I do not know if I have to use subquery in this case, and if I have it, I do not know how to do it, but I'm having a problem; /

    
asked by anonymous 31.12.2015 / 04:43

2 answers

2

Probably only missing GROUP BY: 

SELECT u.login, SUM( c.quantidade ) AS qtd, SUM( f.status ) AS qtdF
FROM usuarios AS u
LEFT JOIN cotas AS c ON c.id_user = u.id
LEFT JOIN faturas AS f ON f.id_user = u.id
GROUP BY u.id

As for COALESCE, it would be interesting to explain what you want, because it does not make much sense as it is, and since you did not explain the data format of f.status , it's a bit tricky to deduce.

What you can, is to use some kind of conditional, for example: 

SUM( IF( f.status IS NULL, 1, 0 ) )

or the other way around.

    
31.12.2015 / 05:03
0

Resolved

I solved this question by doing the following command: 

SELECT u.nome, (
SELECT COALESCE(SUM( c.quantidade ), 0) AS qtd
FROM cotas AS c
WHERE c.id_user = u.id
AND c.status =1
) AS quantidade_cotas, (
SELECT COALESCE(SUM(IF(f.id, 1, 0)),0) AS qtdF FROM faturas AS f
WHERE f.id_user = u.id
) AS quantidade_faturas
FROM usuarios AS u
    
31.12.2015 / 05:03
Error printing mysql data [duplicate] wkHtmlToPdf - Header and footer on all pages