Cross two tables and link the line one with another, forming a third

Question

Cross two tables and link the line one with another, forming a third

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

Good afternoon, fellow programmers.

I came across the following problem: I have two tables, one with applicants, and one with vacancies. I need a query that crosses the two tables, and get the first available slot and give it to the first requester, thus doing a third table. I tried to do it in several ways, but whenever I try, the result ends up giving me a third table with each applicant picking up all the vacancies in the table. (applicant 1 takes all vacancies, then applicant 2 takes all vacancies again, and so on)

The tables are a bit long, so I'll just give the main information for better understanding:

Seekers:

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

Jobs:

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

The answer I need is for it to return something like

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

That is, to assign the vacancies available to the first applicants to these vacancies.

At the moment my query looks 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?

mysql sql

asked by anonymous 08.05.2018 / 18:40

2 answers

1

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

What I did below was to find the lowest id of applicant per job:

SQLFiddle - Online Example:

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

08.05.2018 / 19:27

Navbar with upper margin when adding position: absolute; in a text Function to allow only one vote for equipment

score 1 · Accepted Answer

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

To get the remaining columns of the table, re-add the tables (without GROUP BY) and list the generated ID.

It would look like this:

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 SQLFiddle .