Get records from 3 tables with JOIN

Asked

Viewed 65 times

1

I need to get data from 3 tables in the same SELECT. I just need to get user data from the table users, take the quantity of quotas (column quantity_quotas) that the user has of the table quotas (if you have record in this user table) and the number of invoices the user has in the table invoices (if you have too). I tried to do so:

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 double the amount in the columns Qtd and qtdF.

He was supposed to call me back like:

alissonacioli | 14 | 2

And he’s returning me

alissonacioli | 28 | 4

I don’t know if you have to subquery in that case, and I don’t know how to do it, but I have a problem ;/

php mysql join

  • What are you trying with this COALESCE( f.status, 1, 0 )? And "quotas" is what? An amount of quotas that varies by record?

  • In fact it would be just COALESCE(f.status, 0), pq if it is NULL it leaves as 0. cotas is a table where there is a column called quantity that has varying values and if you have user records, then it should add up each column quantity of that user. I made a command Mysql now here that worked the way you wanted. I will post here, if you can analyze and have a good exit :)

2 answers

2

Probably just 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 the COALESCE, it would be interesting for you to explain what you want, because it does not make much sense as it is, and as you did not explain the format of the data f.status, It’s kind of hard 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 round.

0

Solved

I solved my question by making the 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

Browser other questions tagged php mysql join

You are not signed in. Login or sign up in order to post.