Skip site navigation (1) Skip section navigation (2)

Re: Subqueries

From: "Pascal Tufenkji" <ptufenkji(at)usj(dot)edu(dot)lb>
To: "'Helio Campos Mello de Andrade'" <helio(dot)campos(at)gmail(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Subqueries
Date: 2008-10-31 09:26:12
Message-ID: 004701c93b3a$b913e300$150fa8c0@interne.usj.edu.lb (view raw or flat)
Thread:
Lists: 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

 

 

 

  _____  

From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Helio Campos Mello de Andrade
Sent: Thursday, October 30, 2008 2:53 PM
To: ptufenkji(at)usj(dot)edu(dot)lb
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Subqueries

 

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 <http://p.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
<http://mg.id/> 

      INNER JOIN planification_ens_v pe ON pe.planification_id = pl.id
<http://p.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 <http://pl.id/> ) * 1.5 is the same that
you are looking for because it will depend of what you ate looking for.

Regards

On Thu, Oct 30, 2008 at 10:17 AM, Pascal Tufenkji <ptufenkji(at)usj(dot)edu(dot)lb>
wrote:

Hello,

 

I don't understand the following error.

Can anyone help me plz

Thx

Pascal 

 

select *

from sip_vacations_v v

left join

(

      select pe.emp_id,mg.mat_id,mg.groupe,count(p.id) * 1.5 as nb_heures

      from mat_grp_v mg

      inner join planification_v p on p.mat_grp_id = mg.id

      inner join planification_ens_v pe on pe.planification_id = p.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

 

ERROR:  invalid reference to FROM-clause entry for table "v"

LINE 9: where mg.annee_univ = v.annee and mg.semestre = v.sem_civ

                              ^

HINT:  There is an entry for table "v", but it cannot be referenced from
this part of the query.

 

 

 




-- 
Helio Campos Mello de Andrade

In response to

  • Re: Subqueries at 2008-10-30 12:52:57 from Helio Campos Mello de Andrade

Responses

pgsql-sql by date

Next:From: Gregory StarkDate: 2008-10-31 10:58:40
Subject: Re: Subqueries
Previous:From: A. KretschmerDate: 2008-10-31 07:48:54
Subject: Re: Date Index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group