How to SELECT to pick up data in different tables and with different id’s

Question

Has 3 tables

• On the table entrada has the data: id_entrada, id_empresa, id_categoria and id_subcategory.

• On the table cat_entradas have: id_categoria and categoria.

• And on the table sub_cate_entrada have: id_subcategoria and subcategoria.

I already got the two ids (id_category and id_subcategory) in the input table.

Now, I need to take the category and the subcategory in their respective tables. Can I do this in an SQL command only, or I will have to do 2, one for each table?

    <?php
    $pdo = conectar();
    $this->dataConta=$pdo->prepare("SELECT categoria, subcategoria FROM entrada WHERE id_entrada=:id_entrada AND id_empresa=:id_empresa"); 
    $this->dataConta->bindValue(":id_entrada", 30); 
    $this->dataConta->bindValue(":id_empresa", 1); 
    $this->dataConta->execute(); 
    $res = $this->dataConta->fetch(PDO::FETCH_ASSOC);
    echo $res['categoria']." - ". $res['subcategoria'];
    $pdo = null;

Answer 1

You can use two joins, a single query returns all the results you need:

$resultado = $pdo->query(
    "SELECT e.id_categoria, e.id_subcategoria, c.categoria, s.subcategoria
    FROM entrada e
    INNER JOIN cat_entradas c ON c.id_categoria  = e.id_categoria
    INNER JOIN sub_cate_entrada s ON s.id_subcategoria = e.id_subcategoria"
);
while ($r = $resultado->fetch(PDO::FETCH_ASSOC)) {
    echo $r['id_categoria'] . ': ' . $r['categoria'] . '; ' . $r['id_subcategoria '] . ': ' . $r['subcategoria'] . '#';
}

So reads fields from multiple tables at the same time. The letters "and", "c" and "s" are alias for the tables.

The example prints values for each line read.