Обсуждение: Subqueries

Поиск

Список

Период

Сортировка

Subqueries

От

"Pascal Tufenkji"

Дата:

30 октября 2008 г., 12:34:22

<div class="Section1"><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">Hello,</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">I don’t understand the following error.</span></font><p class="MsoNormal"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Verdana">Can anyone help me plz</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana">Thx</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">Pascal </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">select *</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">from sip_vacations_v v</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">left join</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">(</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">      select pe.emp_id,mg.mat_id,mg.groupe,count(p.id) * 1.5 as nb_heures</span></font><p
class="MsoNormal"><fontface="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">      from mat_grp_v mg</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">      inner join planification_v p on p.mat_grp_id = mg.id</span></font><p
class="MsoNormal"><fontface="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">      </span></font><font face="Courier New" size="2"><span lang="FR"
style="font-size:10.0pt;font-family:
"Courier New"">inner join planification_ens_v pe on pe.planification_id = p.id</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span lang="FR" style="font-size:10.0pt;font-family:"Courier New"">     
</span></font><b><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New"; 
font-weight:bold">where mg.annee_univ = v.annee and mg.semestre = v.sem_civ</span></font></b><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">      group by pe.emp_id,mg.mat_id,mg.groupe</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">) p on p.emp_id = v.emp_id</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">      and p.mat_id = v.mat_id</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">      and p.groupe = v.groupe</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New""> </span></font><p class="MsoNormal"><b><font color="red" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew";color:red;font-weight:bold">ERROR:  invalid reference to FROM-clause
entryfor table "v"</span></font></b><p class="MsoNormal"><b><font color="red" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew";color:red;font-weight:bold">LINE 9: where mg.annee_univ = v.annee and
mg.semestre= v.sem_civ</span></font></b><p class="MsoNormal"><b><font color="red" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew";color:red;font-weight:bold">                             
^</span></font></b><pclass="MsoNormal"><b><font color="red" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew";color:red;font-weight:bold">HINT:  There is an entry for table "v",
butit cannot be referenced from this part of the query.</span></font></b><p class="MsoNormal"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana"> </span></font></div>

Re: Subqueries

От

"Oliveiros Cristina"

Дата:

30 октября 2008 г., 12:49:57

alias v not visible in sub-query?

----- Original Message -----
From: Pascal Tufenkji
To: pgsql-sql@postgresql.org
Sent: Thursday, October 30, 2008 12:17 PM
Subject: [SQL] Subqueries

Hello,

I dont 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.

Re: Subqueries

От

"Helio Campos Mello de Andrade"

Дата:

30 октября 2008 г., 12:53:08

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

On Thu, Oct 30, 2008 at 10:17 AM, Pascal Tufenkji <ptufenkji@usj.edu.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

Re: Subqueries

От

"Pascal Tufenkji"

Дата:

31 октября 2008 г., 09:26:51

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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Helio Campos Mello de Andrade
Sent: Thursday, October 30, 2008 2:53 PM
To: ptufenkji@usj.edu.lb
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Subqueries

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

On Thu, Oct 30, 2008 at 10:17 AM, Pascal Tufenkji <ptufenkji@usj.edu.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

Re: Subqueries

От

Gregory Stark

Дата:

31 октября 2008 г., 10:58:49

"Pascal Tufenkji" <ptufenkji@usj.edu.lb> writes:

> 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 ?

It's not that inner queries can't refer to outer queries. When they do it's
called a "correlated subquery" and it has to be executed once for every row of
the outer query.

It's that queries on one side of a join can't refer to tables on the other
side of the join.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production
Tuning

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Subqueries

Subqueries

Re: Subqueries

Re: Subqueries

Re: Subqueries

Re: Subqueries