CODEWITHSUNDEEP
mysql
Livio Macchia
Livio Macchia

Reputation: 43

mysql sub-query nested pass value

I have this query on MySql:

SELECT id , ( 

    SELECT IFNULL(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(tfo.a,  tfo.da)))),'00:00')  as tempo
    FROM (
        SELECT DISTINCT gg_settimana, id_fascia 
        FROM operatori_piano
        WHERE id_istruttore = o.id
    ) as sa
    LEFT JOIN tab_fasce_orarie as tfo ON tfo.id = sa.id_fascia

) as tempo
FROM operatori as o 
WHERE tipo = 2 AND attivo = 1

Mysql return this error:

Error Code: 1054. Unknown column 'o.id' in 'where clause'

I have tried a lot of combination, but not work. Please a need your help, thanks a lot!

Upvotes: 0

Views: 392

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133400

The o alias in not visibile inside the subquery but looking at your code could avoid the reference to the external select using an internal join 

  SELECT id , ( 

      SELECT IFNULL(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(tfo.a,  tfo.da)))),'00:00')  as tempo
      FROM (
          SELECT DISTINCT gg_settimana, id_fascia 
          FROM operatori_piano
          INNER JOIN operatori as o ON  id_istruttore = o.id
          WHERE o.tipo = 2 AND o.attivo = 1
      ) as sa
      LEFT JOIN tab_fasce_orarie as tfo ON tfo.id = sa.id_fascia

  ) as tempo
  FROM operatori 
  WHERE tipo = 2 AND attivo = 1

Upvotes: 0

symcbean
symcbean

Reputation: 48387

The error occurring is due to you nesting the select statement inside a select inside the query which names o as an alias. Alias are only visible in the immediate sub query.

Without a lot more information about your schema its not possible to advise what a correct, working solution is (its straightforward to produce a query using what we know of your schema which is syntactically correct, but it might not be functionally correct). However, as a general rule of thumb, try to avoid correlated sub queries (pushed predicates). In addition to generally being bad for maintenance and performance, MySQL is notably poor at optimizing such queries.

Upvotes: 0

Related Questions