Doubt about using the Inner Join

Question

I have 4 tables in my database

Tabela Cadastro
     - idCadastro(PK)
     - IP

Tabela Porta
     - idPorta(PK)
     - numero porta
     - idSwitch(FK)

Tabela Porta_has_Cadastro
     - idCadastro(FK)
     - idPorta(PK)

//Observação: Relaçao n para n entre cadastro e porta.

Tabela Switch
     - idSwitch
     - Modelo

I want the information to come out like this:

IP  ----  PORTA  ----  SWITCH

Example:

O ip 192.168.0.1 esta na porta 1 do Switch HP
O ip 192.168.0.2 esta na porta 2 do Switch Dell

Answer 1

Try this query:

SELECT cad.IP,
        por.numero_porta,
        swi.modelo
FROM cadastro cad
  INNER JOIN Porta_has_Cadastro phc ON cad.idCadastro = phc.idCadastro
  INNER JOIN Porta por ON phc.id_porta = por.id_porta
  INNER JOIN Switch swi ON por.id_switch = swi.id_switch

Then if you want to bring the complete sentence straight from the database, you can use string concatenation, but ai depends on the data type of each column, and conversion of type may be required, since concatenation depends on the database, of which I know, can be + for Sql Server and || for Postgres.

Answer 2

[Assuming]

Without specifying which of the two forms you want, you can do the following:

SELECT 'O ip ' + C.IP + ' esta na porta ' + CAST(P.numero_porta AS nvarchar) + ' do Switch ' + s.Model
FROM dbo.Switch as s
     inner join dbo.Porta as P on p.idSwitch = s.idSwitch
     inner join dbo.Porta_has_Cadastro as PhC on PhC.idPorta = p.idPorta
     inner join dbo.Cadastro as C on C.idCadastro = PhC.idCadastro;

Or else:

SELECT C.IP, P.numero_porta, s.Model
FROM dbo.Switch as s
     inner join dbo.Porta as P on p.idSwitch = s.idSwitch
     inner join dbo.Porta_has_Cadastro as PhC on PhC.idPorta = p.idPorta
     inner join dbo.Cadastro as C on C.idCadastro = PhC.idCadastro;

See a example in Sqlfiddle.