How to do an INNER JOIN bringing everything from two tables and one more "max" in a specific field?

Question

Good guys, to be more specific I have two tables,. table 01:

...and table 02:

...and would like to make a "select" bringing all of these two tables, but where in the field "points" (table 02) have the highest number of points, how to do this?

Ex.: Alex Bernardes, 20,00, 15,00, 35,00, 01/11/2017 and points: 110.

Answer 1

SELECT tabela1.*, tabela2.pontos, tabela2.garcom_id FROM tabela1 JOIN tabela2 WHERE tabela2.garcom_id = tabela1.id

So you do the Join of the 2 tables, returning only the points and the field garcom_id (it is necessary to make the comparison).

Answer 2

For this case you should use sub-query, so you only take the largest of table 2.

SELECT tabela1.*,tabela2.* FROM tabela1 INNER JOIN (SELECT * FROM tabela2 WHERE pontos = (SELECT MAX(pontos) FROM tabela2)) as tabela2 ON tabela1.Id = tabela2.garcom_id ;

Answer 3

All you have to do is make a SELECT using the clause INNER JOIN, that works for a junction of two or more tables. Therefore, I will leave below an example of how you should do to join the tables bringing only the field pontos of Table 2

SELECT tabela1.*, tabela2.pontos, FROM tabela1 INNER JOIN tabela2 WHERE tabela1.id = tabela.garcom_id

Recalling that the .* is to indicate that it will bring all records from table 1