I have two tables I need to do a SELECT in both at the same time, basically it will work like this:
Note that the result returns the column nome of Tabela A and sum of column media where ids are equal. Also, names with no record in Tabela B are displayed with a value of 0.
I searched the site for something similar and could not find, how can I do that?
To get the expected result you need to make a JOIN between the tables. In addition, you need to use aggregate functions with GROUP BY to be able to group and sum the values.
Your query would look like this:
SELECT a.nome,
COALESCE(SUM(b.media), 0) AS media
FROM tabela_a a
LEFT JOIN tabela_b b ON b.id_usuario = a.id
GROUP BY a.nome;
One note: In the example you exposed JOSÉ added values , but MARIA resulted in average values , so it got a bit confusing. If you want to calculate the mean and not the sum, you should use the AVG function instead of SUM .
SELECT a.nome,
COALESCE(AVG(b.media), 0) AS media
FROM tabela_a a
LEFT JOIN tabela_b b ON b.id_usuario = a.id
GROUP BY a.nome;
A HERE example to run on SQLFiddle .
As the information is distributed by two tables you need to relate them through the existing link between the primary and foreign keys ).
We get this from using JOIN . But there is an important difference between using INNER JOIN and LEFT JOIN .
With INNER JOIN the result of query is limited to records that fully meet the binding conditions in the two tables , that is, for each record in table A we have you find one or more matching records in table B.
If we used INNER JOIN in the example the result of query would only bring the JOSÉ and MARIA records , because for JOÃO and ANA we did not find related records in table B.
Already with LEFT JOIN the result of query matches the total of records from the table found on the left side of the relationship in our In this case, with the use of LEFT JOIN we were able to bring all the names in the result, regardless of the information in table B.
Through% w we group records of query and we can apply the aggregation functions on the fields to perform counting operations (% ( GROUP BY ), calculation of average ( COUNT ), sum ( AVG ), among others.
As we will not have information about JOÃO and ANA in table B the result of applying an aggregate function to your records, be the average (% with%) or the sum ( SUM ), will be null ( AVG ).
The SUM function allows NULL to be replaced by another value , 0 in the examples. It evaluates all reported parameters and always returns the first one other than null .
COALESCE returns the second parameter, value 1. NULL returns the first parameter, value 2. COALESCE(NULL, 1, 3) returns the mean if different from COALESCE(2, 1) , otherwise 0. In this particular case we could also use the function COALESCE(AVG(b.media), 0) (which only accepts 2 parameters, while NULL can evaluate several parameters) with the same result as IFNULL .
How to display data from a foreign key table in my main table?