How to make more than one select in the same query in different DB tables?

Question

How to make more than one select in the same query in different DB tables?

Navigation

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

0

The problem is as follows:

I have the tables:

Whatareforeignkeysinthefollowingtable:

AndIneedtopullallthenamesofthe"connector_model", "connector_name" and "connector_type" columns and concatenate and echo an individual search.

To do this, I tried the following query, but it did not work, because you need to make multiple selects in the same query: Note: in this attempt I only put the selects of the model_connector_tables, the_type_connectors and connectors, I could not fit the_gear_connectors 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, it is always returning this error:

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

php query select mysqli

asked by anonymous 06.03.2017 / 19:07

3 answers

1

Your role is causing an error. After calling the mysqli_query function you can call the mysqli_error function to check for any problems.

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

A simple example of usage:

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);

06.03.2017 / 19:43

1

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

If it is mandatory in the connectors table, always be registered an id of each table I believe 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

06.03.2017 / 19:43

Swap String contents for "$" How and why getElementById does not execute on page load?

score 2 · Accepted Answer

The error seems to be in your connection to the database (I imagine it is from the home of the file), I would need to show the code on it to confirm. Does your select return the data you want to run it in the 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