Home / Questions / 462270
Accepted
Enrique Espinosa
Asked: 2022-06-16 14:52:28 +0800 CST 2022-06-16 14:52:28 +0800 CST

INNER JOIN gives me an error when querying a table

  • 772

I have a problem, I am doing a query composed of two INNER JOINs to retrieve information from 3 different tables, the problem is that when I put the ON clause and put the following egresses.account = 15 , it gives me the following error:

The column 'egresos_test.cuenta' in on clause is unknown

This is my full query:

> SELECT cuentas.no_cuenta, sum(saldo) saldo from
> bank_log INNER JOIN cuentas ON
> cuentas.id_cuenta = 15 INNER JOIN (SELECT sum(cantidad)
> egresosTotal FROM egresos WHERE cuenta = 15) AS egresosTotal ON
> egresos.cuenta = 15 WHERE bank_log.no_cuenta = 15 ORDER BY
> bank_log.id_registro DESC LIMIT 1

I do not understand why it does not find the clomuna account in the second INNER JOIN where I add the amount, if I am placing egress.account.

Update:

I have tried using this solution:

SELECT cuentas.no_cuenta, sum(saldo) saldo 
  from bank_log 
       INNER JOIN cuentas ON cuentas.id_cuenta = 15 
       CROSS JOIN (SELECT sum(cantidad) egresosTotal 
                     FROM egresos 
                    WHERE cuenta = 15) AS egresosTotal 
 WHERE bank_log.no_cuenta = 15 
 ORDER BY bank_log.id_registro DESC 
 LIMIT 1

But the problem now is that when executing the query it does not print the result of egresosTotal, it only prints two columns of 3.

enter image description here

mysql

1 Answers

  1. Best Answer

    From what I see, you have not well defined the issue of the visibility of the identifiers when using subqueries.

    When you write a subquery, inside a pair of parentheses, (select columna1, columna2 from tabla1 iner join tabla2 on x = y), the tables and aliases that you use inside the subquery are only known within the subquery itself (inside the parentheses), but not outside of it. Outside of it, only the names or aliases of the columns returned by the subquery will be known, and they can be qualified with the alias that you put on it, but not with the internal aliases, which are unknown at this level.

    That is the case when you do a select from (select from... ) miAlias, as in the example you put.

    Also, since the subquery doesn't return a column named cuenta, you couldn't just change the alias of the condition either, since there's no column to compare against.

    There are several solutions:

    • Change the inner jointo a cross join:
    SELECT cuentas.no_cuenta, sum(saldo) saldo 
  from bank_log 
       INNER JOIN cuentas ON cuentas.id_cuenta = 15 
       CROSS JOIN (SELECT sum(cantidad) egresosTotal 
                     FROM egresos 
                    WHERE cuenta = 15) AS egresosTotal 
 WHERE bank_log.no_cuenta = 15 
 ORDER BY bank_log.id_registro DESC 
 LIMIT 1

    You can include the column in the result of the subquery, and keep the inner join:

    SELECT cuentas.no_cuenta, sum(saldo) saldo 
  from bank_log 
       INNER JOIN cuentas ON cuentas.id_cuenta = 15 
       INNER JOIN (SELECT cuenta, sum(cantidad) egresosTotal 
                     FROM egresos 
                    WHERE cuenta = 15
                    group by cuenta) AS egresosTotal on egresosTotal.Cuenta = bank_log.Cuenta
 WHERE bank_log.no_cuenta = 15 
 ORDER BY bank_log.id_registro DESC 
 LIMIT 1

    That said, your query has too many references to the constant 15. Every time you want to check a different account, you will have to change that value in many places. You can write the same query by referencing between the columns that relate the tables in the joins, and place the constant only in the clause where, for example:

    SELECT cuentas.no_cuenta, sum(saldo) saldo 
  from bank_log 
       INNER JOIN cuentas ON cuentas.id_cuenta = bank_log.no_cuenta
       CROSS JOIN (SELECT sum(cantidad) egresosTotal 
                     FROM egresos 
                    WHERE egresos.cuenta = bank_log.no_cuenta) AS egresosTotal 
 WHERE bank_log.no_cuenta = 15 
 ORDER BY bank_log.id_registro DESC 
 LIMIT 1

    As you can see, now the number 15 only appears once, and the query keeps returning the same result.

    • 2