Select within select sorted by second query mysql

Navigation
0

I have a problem, I do not know if I'm doing it right, but come on.

I have a table that lists some employees (tblTecnicos).

I have another table, which has the service registers that these technicians performed (tblServices), in this table, has a field that calls "service type", in it, has the id of some service that the technician performed.

Ex: Services:

1 - maintenance 2 - formatting

In the tblServices table

  • Technical TypeService
  • 5 2
  • 5 1
  • 2 2
  • 1 1
  • 5 2
  • 2 1

Type, if I put it to list the technician, would list:

  • idTecnico 1
  • idTecnic 2
  • idTecnico 5

etc

I would like to do a select, listing all the technicians, sorted by the highest amount of services they performed.

In the example, the technician with ID 1 did 1 service, the technician with id 2 did 2 services and the technician with ID 5 did 3 services.

At the time of selecting, I'd like you to come sorted by the most services you've done.

  • idTecnico 5
  • idTecnic 2
  • idTecnico 1

I tried this way, but it did not work: 

SELECT * FROM tblTecnicos where (select count(tipoServico) from tblServicos where tipoServico > 0 order by(tipoServico)DESC)

Does anyone know if you can?

EDITION

I was able to solve some of the problem with the code below, but it is only returning 1 result. What's wrong ? 

SELECT tblTecnicos.nome, count(tblServicos.tipoServico) 
FROM tblTecnicos 
    INNER JOIN tblServicos ON tblServicos.tipoServico > 0
AND tblTecnicos.id = tblServicos.idTecnico 
ORDER BY tblServicos.tipoServico DESC
    
asked by anonymous 04.09.2015 / 17:07

2 answers

1

The problem is that you have to do count of tipoServico in ORDER BY : 

SELECT * 
FROM tblTecnicos 
WHERE tipoServico > 0
ORDER BY COUNT(tipoServico) DESC

If you wanted to display the result by displaying (only idTecnico ), you have to make a GROUP BY : 

SELECT idTecnico 
FROM tblTecnicos 
WHERE tipoServico > 0
GROUP BY idTecnico 
ORDER BY COUNT(tipoServico) DESC
    
04.09.2015 / 17:12
0

After studying a bit about JOIN of MYSQL, I was able to solve my problem.

Here's how it went: 

SELECT a.id,a.nome,count(b.tipoServico) 
FROM tblTecnicos AS a 
INNER JOIN tblServico AS b 
ON (a.id = b.idTecnico) 
GROUP BY a.nome 
ORDER BY COUNT(b.tipoServico) DESC 
LIMIT 12
    
04.09.2015 / 22:52
Redirect any html extension URL to a specific page Performing a specific task on a specific date and time