SQL query unifying 3 tables

Asked

Viewed 304 times

3

I have 4 tables that I need to join them in a single query where the result is:

nome |COUNT(terrenosDeCadaID)|COUNT(conversasDeCadaID)
joao |         2             |       3
maria|         1             |       2

The tables are 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 beginner, and 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
sql inner-join

  • Your idea is correct, but the second Join with table conversations that is with the wrong reference: INNER JOIN conversas c on u.id =t.iduser it should be like this: INNER JOIN conversas c on u.id =c.iduser

2 answers

3


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

It was a mistake because the field nome was not in the GROUP BY. The consultation would never give the result you expected because it had two INNER JOIN a two tables with different associations to the table user, and even correcting the GROUP BY whether it would be possible to correctly account for occurrences in each of the tables.

  • got it, funciooun, only had one though, have a user who has no conversation, then did not appear in the query, have to make it appear in the query with conversations "0"?

  • Edited answer to answer your question. Basically, we switched the INNER JOIN for LEFT JOIN and applied a ISNULL fields that may have values NULL (due to lack of information in the tables).

  • he gave this error: #1582 - Incorrect Parameter Count in the call to Native Function 'ISNULL'

  • Amended the ISNULL for IFNULL (did not initially know it was Mysql).

  • show, gave right, just for me enteder, you used the left Join and made as if it was a consultation inside the other, this?

  • @Leandromarzullo As a suggestion, I would put in the question (in this and in the next) the tag [tag:mysql], so everyone who will answer (or consult, in this case, that has already been answered) will already know which bank it is.

  • 1

    ah blz, Thank you!

  • 1

    To answer your question @Leandromarzullo, yes, the LEFT JOIN allows to obtain information from the associated tables having or not associated records (unlike the INNER JOIN, which "obliges" the iduser exist in all linked tables), and we call a direct query, associating the iduser of this query with the table iduser users.

  • show, now I understood well the difference of Jay and left, I’m even applying in other consultations here...rs

Show 4 more comments

0

If you are using the MS SQL Server can do so:

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

Testing: http://sqlfiddle.com/#! 18/c56b1/11

Browser other questions tagged sql inner-join

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