# How to count records of 3 tables linked by a foreign key?

2

I have a main table with one record, and in the other two tables there can be several records that depend on the record in table 1.

I need you to show me only 1 record as it appears in image 1, but adding 3 more columns corresponding to:

1 - Sum of the values of the 'valued' column in table 2 that coincide with the primary key of table 1 and the number of rows added.

2 - Total records in table 3 that match the primary key in table 1.

I have this sql but the sum does not work for me:

``````SELECT count(DISTINCT(p.idpagos)) as pagos, sum(valorabonado), count(DISTINCT(e.identrada)) FROM pagoperiodico pg
INNER JOIN pagos p USING(idpago)
INNER JOIN entrada e ON pg.idpago=e.idpago
WHERE pg.idpago=7334
GROUP BY pg.idpago
``````

asked by Nelson Sepulveda 25.01.2018 в 06:51
source

0

Well, since you have accepted the previous answer, I am certain that the key that relates the three tables is `idpagoperiodico` , so a possible solution could be:

``````SELECT  pg.idpagoperiodico,
FROM pagoperiodico pg
LEFT JOIN (SELECT idpagoperiodico,
FROM pagos
GROUP BY idpagoperiodico
) p
ON p.idpagoperiodico = pg.idpagoperiodico
ON e.idpagoperiodico = pg.idpagoperiodico
GROUP BY pg.idpagoperiodico,
``````

Explanation:

• The first `LEFT JOIN` points to a subquery that groups each `idpagoperiodico` and gets the sum of `valorabonado` and the number of records we have grouped in `pagos` .
• The next `LEFT JOIN` points directly to `entrada` but there would be no problem with row repetitions since we are grouping at the end and counting the records in this table by `COUNT(e.identrada)`
• The idea of using `LEFT` instead of `INNER` is simply to always have all the `pagoperiodico.idpagoperiodico` even if they do not have rows in the tables `pagos` and `entrada` , now if only you are interested in those cases where there is relationship with any of the two tables, simply modify the `LEFT` by `INNER` .

Note : I suggest you always indicate the appropriate database engine tag, in the question you indicated `postgres` and `mysql` , in this case I think the solution should work for both cases, but remember that SQL has language differences between the different engines.

source
2

Dividing the question into parts:

1-Sum of the values of the 'valued' column in table 2 that coincide with the primary key of table 1 and the number of rows added

With the function SUM () you have the summation of 'valuebonded' and with the function COUNT () the number of rows added. Doing a JOIN of Table 1 and 2 you have the result:

``````SELECT COUNT(*),
FROM T1 JOIN T2 ON (T1.Id = T2.Id)
WHERE T1.Id = 7334
``````

2- Total records in table 3 that match the primary key in table 1.

This is also achieved with a COUNT (*):

``````SELECT COUNT(*)
FROM T3
WHERE T3.Id = 7334
``````

And to 'package' everything in a cosulta, you can put the second query as a nested query. It would look like this:

``````SELECT COUNT(*) AS COL_NAME1,