Cross two tables and link the line from one to the other, forming a third

Question

Good afternoon, fellow programmers.

I came across the following problem: I have two tables, one with applicants, and the other with vacancies. I need a query that crosses the two tables, and takes the first available vacancy and gives to the first requester, thus making a third table. I tried to do it in several ways, but every time I try, the result ends up giving me a third table with each requester taking all the vacancies in the table. (Requester 1 picks up all vacancies, then Requester 2 picks up all vacancies again, and so on)

The tables are a little extensive, so I will give only the main information for better understanding:

Applicants:

+----+-------------+----------+
| id | Solicitante | id vaga  |
+----+-------------+----------+
| 01 | Joãozinho   |   12345  |
| 02 | Mariazinha  |   12345  |
| 03 | Bruno       |   54321  |
| 04 | Celia       |   54321  |
| 05 | André       |   99999  |
+----+-------------+----------+

Vacancies:

+-----+---------+
| id  | id vaga |
+-----+---------+
| 01  |   12345 |
| 02  |   54321 |
| 03  |   99999 |
| 04  |   99999 |
+-----+---------+

The answer I need is for him to return something like

+-------------+----------+
| Joãozinho   |    12345 |
| Bruno       |    54321 |
| André       |    99999 |
+-------------+----------+

that is, allocate the available vacancies to the first applicants for these vacancies.

At the moment my query is like this:

SELECT C.Nome_Solic, C.Especialidade,P.Nome_Med,P.Data_Consul,P.Hora_Consul
  FROM [DB_PMSP_CONSULTA].[dbo].[Tabela_Solicitante_HSPM] AS C 
 INNER JOIN [DB_PMSP_CONSULTA].[dbo].[Vagas_Disponiveis_HSPM] AS P ON 
C.Especialidade = P.Espec 

Would anyone have any idea how I can do what I need?

Answer 1

According to the result you expect it is not necessary to involve a second different table because all the data are contained in the first.

What I did below was get the smallest requester id per vacancy:

Sqlfiddle - Online example:

SELECT 
   MIN(ID) AS IDPrimeiroSolicitante
   , IDVAGA AS VagaPrimeiroSolicitante
   , Solicitante
FROM Solicitantes
GROUP BY IDVAGA

Answer 2

For what you need, first you need to eliminate the duplicity of vacancies of the first table (Requesters) and the second table (Vacancies) and then relate them by id_vaga.

To get the other table columns, re-include the tables (without GROUP BY) and relate by the generated ID.

Would look this way:

SELECT C.Solicitante, D.ID_Vaga FROM 
  (SELECT MIN(ID) AS ID, ID_Vaga FROM Solicitantes GROUP BY ID_Vaga) AS A
INNER JOIN 
  (SELECT MIN(ID) AS ID, ID_Vaga FROM Vagas_Disponiveis GROUP BY ID_Vaga) AS B
  ON A.ID_Vaga = B.ID_Vaga
INNER JOIN Solicitantes C ON A.ID = C.ID
INNER JOIN Vagas_Disponiveis D ON B.ID = D.ID;

See in Sqlfiddle.