Re: Subqueries - Mailing list pgsql-sql

Hi Helio,

I understand the fact that “the inner query is executed before the outer query and the inner query doesn't even know about the outer query.”

But why the following query can be executed, although the inner query is using the outer query.

Aren’t we here using the same concept ?

SELECT

e1.empno,e1.ename,e1.job,e1.deptno,

(select count(e2.empno) from emp e2 where e2.deptno = e1.deptno) as "Total"

from emp e1;

 empno | ename  |    job    | deptno | Total

-------+--------+-----------+--------+-------

  7839 | KING   | PRESIDENT |     10 |     3

  7698 | BLAKE  | MANAGER   |     30 |     6

  7782 | CLARK  | MANAGER   |     10 |     3

  7566 | JONES  | MANAGER   |     20 |     5

The "v" reference need to exist in the inner query. You can't use an "outer query reference" in the inner query.
This happens because the inner query is executed before the outer query and the inner query doesn't even know about the outer query.

May be this helps you get what you want.

SELECT *, count(pl.id) * 1.5 AS nb_heures

FROM sip_vacations_v v

LEFT JOIN

      (

      mat_grp_v mg

      INNER JOIN planification_v pl     ON pl.mat_grp_id       = mg.id

      INNER JOIN planification_ens_v pe ON pe.planification_id = pl.id

      WHERE mg.annee_univ = v.annee and mg.semestre = v.sem_civ

      GROUP BY pe.emp_id, mg.mat_id, mg.groupe

) p

ON p.emp_id = v.emp_id AND p.mat_id = v.mat_id AND p.groupe = v.groupe;

I not sure about the count(pl.id) * 1.5 is the same that you are looking for because it will depend of what you ate looking for.

Regards

--
Helio Campos Mello de Andrade