Select Mysql query used ports?

Question

Personal I am needing to make a query from which return me the unused values, using 3 tables from which return me which ports are not being used in a network switch

first table

sw_local id, nome

second table

sw_local_cliente id, id_cliente, id_sw, id_porta

third table

sw_porta id, porta

With sw_port value from 1 to 16

I need to know which ports are not being used on the switch’s.

Answer 1

I did using IN, but can also be done using NOT EXISTS.

If you want to know which particular ports switch you are not using could do so:

select switch.nome,
       (
         select group_concat(porta.porta)
         from sw_porta as porta
         where porta.id not in (
                                 select switch_cliente.id_porta
                                 from sw_local_cliente as switch_cliente
                                 where switch_cliente.id_porta = porta.id and
                                       switch_cliente.id_sw = switch.id
               )
       ) as portas_nao_utilizadas
from sw_local as switch

If you want to know which port is not being used on any switch, could do so:

select
  *
from
  sw_porta as porta
where
  porta.id not in (
    select
      switch_cliente.id_porta
    from
      sw_local_cliente as switch_cliente
  )

That’s what you want?