How to make an INNER JOIN by bringing specific fields from two tables and removing the repeated records and with a max condition?

Question

How to make an INNER JOIN by bringing specific fields from two tables and removing the repeated records and with a max condition?

Navigation

#1 by (2 votes)

0

Well, I have the following tables:

Table 01:

Andtable02:

Then I would like to bring only all the fields with bigger points and do not repeat the waiter in case, how to do it?

mysql query phpmyadmin

asked by anonymous 19.11.2017 / 22:08

1 answer

Relationship between tables of same values in columns Select row from a gridview and pass values to a textbox

score 2 · Accepted Answer

Adding the same waiter's points (eg Juliana Oliveira => 100 + 123)

SELECT g.nome Garcom,sum(p.pontos) TotalPontos
FROM tabela01 g
JOIN tabela02 p ON p.garcom_id = g.id
GROUP BY g.nome 
ORDER BY sum(p.pontos) DESC,g.nome

Without adding , taking the highest point of each waiter

SELECT g.nome Garcom,max(p.pontos) maior
FROM tabela01 g
JOIN tabela02 p ON p.garcom_id = g.id
GROUP BY g.nome 
ORDER BY max(p.pontos) DESC,g.nome

Q.: Change the tabela01 and tabela02 to the name of the respective tables

If you want the first records to filter through the first N records that you want and you did not report what kind of database it is.

SqlServer would add a TOP N shortly after the select ( SELECT TOP 10 .... );

PostgreSql would add a LIMIT N at the end ( ORDER BY ..... LIMIT 10 );

Oracle is beemmm more complicated, nor will I post here, I will report a link (I found it in Google) Oracle

MySql is equal to PostgreSql, use LIMIT at the end