Total Subrecords - Mysql

Question

In my database I have several tables, but I’ll make a simple example of what I want to do:

Assuming two tables:

• Table 1

id | store | date

• Table 2

id_2 | id_tab1_2 | product

Note that the id_tab1_2 is a foreign key, linked to the field id of table 1.

What I want is IN A SINGLE QUERY bring all records from Table 1 and the amount of sub-records of the Table 2, ex:

SELECT * FROM tabela_1 ...

And the result was something like this:

id | store | date | quant_tab_2

1 | X | 23/07/2018 | 2

2 | Y | 23/07/2018 | 1

3 | Z | 23/07/2018 | 0

On the field quant_tab_2 comes the quantity if subrecords in the Table 2 of each record of Table 1

Not that I have to see a column with the name quant_tab_2, It’s just an example, I don’t want to have to do for every record of Table 1, a new query to know how many subrecords there are in the Table 2, is it possible to do this? Can anyone help me?

Answer 1

Based on its assumption, the query would look that way:

SELECT
tabela_1.id,
tabela_1.loja,
tabela_1.data,
COUNT(tabela_2.id_2) as quant_tab_2
FROM tabela_1
LEFT JOIN tabela_2 ON (tabela_1.id = tabela_2.id_tab1_2)
GROUP BY tabela_1.id

Answer 2

You can do something like:

select t1. *, Count(t2.product) from Tabela1 as t1

Inner Join table2 as t2 on t2.id_tab1_2 = t1.id

group by t2.id_tab1_2