How to do more than one select in the same query in different BD tables?

Question

The problem is this:

I have the tables:

Which are foreign keys in the following table:

And I need to pull all the column names "modelo_conector", "nome_genero_conector" and "tipo_de_conector" and concatenate and give an echo in a certain search.

For this, I tried to make the following query, but it did not work, because you need to make several selects in the same query: Obs: in this attempt I only put the selects of the connector_models tables, connectores_types and connectors, I could not fit the connectores_generos table:

function listaConectores($conexao) {
$conectores = array();
$resultado = mysqli_query($conexao, 
"select
    c.*
    ct.tipo_de_conector as tipo_de_conector,
    temp_sql.modelo_conector
from
(select
    c.id_conector
    cm.modelo_conector
from
    conectores as c
join
    conectores_modelos as cm
on
    c.conectores_modelos_id_conector_modelo = cm.id_conector_modelo
)
    temp_sql
join
    conectores as c
on
    temp_sql.id_conector = c.id_conector
join
    conectores_tipos as ct
c.conectores_tipos_id_conector_tipo = ct.id_conector_tipo"
);
    while($conector = mysqli_fetch_assoc($resultado)) {
    array_push($conectores, $conector);
}

return $conectores;
}

However, he is always returning me this mistake:

Does anyone have a suggestion or a light that can help me?

Answer 1

The error seems to be in your connection with the database (I imagine it is through the home of the file), would need to show the code in it to confirm. Your select returns the data you want if you run it in Workbench (if it is mysql) or in the tool you use to query/maintain your database?

The select you’re doing would be to return something like this:

select c.*, cm.modelo_conector, cg.nome_genero_conector, ct.tipo_de_conector
from conectores c
left join conectores_modelos cm on cm.id_conector_modelo = c.conectores_modelos_id_conector_modelo
left join conectores_generos cg on cg.id_conector_genero = c.conectores_generos_id_conector_genero
left join conectores_tipos ct on ct.id_conector_tipo = c.conectores_tipos_id_conector_tipo

Answer 2

I did not test, but I believe that you can solve everything with JOINS and not subselects.

If obligatory in table connectors, always be registered an id of each table I think will work.

Try

SELECT cm.modelo_conector, cg.nome_genero_conector, ct.tipo_de_conector
FROM conectores c
INNER JOIN conectores_modelos cm ON c.conectores_modelos_id_conector_modelo = cm.id_conector_modelo
INNER JOIN conectores_generos cg ON c.conectores_generos_id_conector_genero = cg.id_conector_genero
INNER JOIN conectores_tipo ct ON c.conectores_tipos_id_conector_tipo = ct.id_conector_tipo

Answer 3

Your function is causing an error. After calling the function mysqli_query you can call the function mysqli_error to check if there was any problem.

As for your query, I suggest using a more streamlined command such as a JOIN.

A simple example of use:

SELECT
  A.*,
FROM conectores AS A
LEFT JOIN conectores_modelos AS B
  ON (A..conectores_modelos_id_conector_modelo = B.id_conector_modelo)
LEFT JOIN  conectores_tipos as C
  ON (A.conectores_tipos_id_conector_tipo = C.id_conector_tipo);