Help with Query Mysql

Question

I have the following query:

select 
profissionais_ab.cns cns_medico,
profissionais_ab.cbo,
unidade_ab.nome
from 
profissionais_ab
join unidade_ab on unidade_ab.id = profissionais_ab.unidade_id

Return me the following:

123456 | DENTISTA 1 | UNIDADE 1
789012 | DENTISTA 2 | UNIDADE 2

The table profissionais_ab is composed of: id integer varchar cbo varchar cns varnish integer unit

In the query I mentioned above, he pulls the data of a professional (the dentist), but in this same table there is the registration of the auxiliary

1 | DENTISTA 1 | DENTISTA | 123456 | 1
2 | DENTISTA 2 | DENTISTA | 789012 | 2
3 | DENTISTA 3 | DENTISTA | 345678 | 3
4 | AUXILIAR 1 | AUXILIAR | 901234 | 1
5 | AUXILIAR 2 | AUXILIAR | 567890 | 2
6 | AUXILIAR 3 | AUXILIAR | 234567 | 3

I need the result to come both the dentist and the auxiliary of the same unit:

123456 | DENTISTA 1 | UNIDADE 1 | AUXILIAR 1
789012 | DENTISTA 2 | UNIDADE 2 | AUXILIAR 2

Any suggestions?

Answer 1

From what I understand, you want to show the units and which dentist this unit and which auxiliary unit and every unit will have a dentist and an assistant. Correct?

If this is the understanding, you should start your consultation by the unit, which is the main entity in the context of your problem and make a Join with the table of professionals twice, one with restriction for dentists (in the example you show a field where you determine if the professional is a dentist or assistant, but I did not see the name of this field) and the other junction with professionals with restrictions for assistants.

    Select
    unidade_ab.nome,
    dentistas.nome,
    dentistas.cbs,
    auxiliares.nome,
    auxiliares.cbs

from
    unidade_ab

inner join
    profissionais_ab dentistas on dentistas.campo == 'DENTISTA' and dentistas.unidade_id = unidade_ab.id

inner join
    profissionais_ab auxiliares on auxiliares.campo == 'AUXILIAR' and auxiliares.unidade_id = unidade_ab.id

If understanding is what I went through, I believe something like this (adjusting the restriction for DENTAL ASSISTANTS) should solve.

Answer 2

You have left many things open in your clarifications because I do not know what unity is in your example table. Try something like:

SELECT profissionais_ab.cns cns_medico, profissionais_ab.cbo, unidade_ab.nome
FROM profissionais_ab 
    INNER JOIN unidade_ab ON unidade_ab.id = profissionais_ab.unidade_id
    INNER JOIN profissionais_ab AS auxiliar ON (profissionais_ab.campo_de_ligação = auxiliar.campo_de_ligação AND profissionais_ab.cbo <> auxiliar.cbo);