Total Sub-Records - MySql

Question

Total Sub-Records - MySql

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

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

Assuming two tables:

Table 1

id | shop | date

Table 2

id_2 | id_tab1_2 | product

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

What I want is IN ONE SINGLE QUERY to bring all records of Table 1 and the number of Table 2 sub records, ex:

SELECT * FROM tabela_1 ...

And the result was something like this:

id | shop | date | quant_tab_2

1 | X | 23/07/2018 | 2

2 | And | 23/07/2018 | 1

3 | Z | 23/07/2018 | 0

In the quant_tab_2 field, the quantity and sub records in Table 2 of each record in Table 1

quant_tab_2 , it's just an example, I do not want to have to do for every record of Table 1 , a new query to know how many sub records there are in Table 2 , is it possible to do this? Can anyone help me?

mysql

asked by anonymous 24.07.2018 / 04:19

2 answers

1

You can do something like this:

select t1. *, count (t2.product) from table1 as t1

inner join table2 as t2 on t2.id_tab1_2 = t1.id

group by t2.id_tab1_2

24.07.2018 / 05:03

Calling a method How to use LinkedHashSet to implement this IHashSet interface?

score 1 · Accepted Answer

Based on your assumption, query would look like this:

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