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

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