I have 4 tables that I need to join in a single query where the result is:
%pre%The tables look like this:
%pre%I tried to do with INNER JOIN, but I'm still a beginner, and I could not, when I added the conversations went wrong, below what I tried:
%pre%You can reach these values as follows:
%pre%I was giving error because the %code% field was not in %code% . The query would never give the result it expected because it had two %code% to two tables with associations other than the %code% table, and even correcting %code% would not be able to correctly count the occurrences in each of the tables. p>
If you are using %code% you can do this:
%pre%Test: link
I have 4 tables that I need to join in a single query where the result is:
nome |COUNT(terrenosDeCadaID)|COUNT(conversasDeCadaID)
joao | 2 | 3
maria| 1 | 2
The tables look like this:
users
id|nome
1 |joao
2 |maria
terrenos
id|idterreno |iduser(mesmo que id da tabela users)
1 | 20 | 1
2 | 21 | 1
3 | 22 | 2
conversas
id|idterreno|msg|iduser(mesmo que id da tabela users)
1 | 20 | a | 1
2 | 20 | b | 1
3 | 21 | c | 1
4 | 22 | d | 2
5 | 22 | d | 2
I tried to do with INNER JOIN, but I'm still a beginner, and I could not, when I added the conversations went wrong, below what I tried:
SELECT nome, COUNT(t.iduser), COUNT(c.user) FROM users u
INNER JOIN terrenos t on u.id = t.iduser
INNER JOIN conversas c on u.id =t.iduser
GROUP BY t.iduser
You can reach these values as follows:
SELECT u.nome
, IFNULL(t.totalterrenos, 0) AS totalterrenos
, IFNULL(c.totalconversas, 0) AS totalconversas
FROM users u
LEFT JOIN (
SELECT iduser
, COUNT(1) AS totalterrenos
FROM terrenos
GROUP BY iduser
) t ON t.iduser = u.id
LEFT JOIN (
SELECT iduser
, COUNT(1) AS totalconversas
FROM conversas
GROUP BY iduser
) c ON c.iduser = u.id
I was giving error because the nome field was not in GROUP BY .
The query would never give the result it expected because it had two INNER JOIN to two tables with associations other than the user table, and even correcting GROUP BY would not be able to correctly count the occurrences in each of the tables. p>
If you are using MS SQL Server you can do this:
select
A.nome,
B.terrenos,
C.conversas
from users as A
cross apply (select count(1) as terrenos from terrenos where iduser = A.id) B
cross apply (select count(1) as conversas from conversas where iduser = A.id) C
Test: link