Thread: Subqueries

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

----- Original Message -----

From: Pascal Tufenkji

To: pgsql-sql@postgresql.org

Sent: Thursday, October 30, 2008 12:17 PM

Subject: [SQL] Subqueries

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.

 

 

 

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

"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