Re: [SQL] Subqueries

[Gregory Stark]

"Pascal Tufenkji" <[EMAIL PROTECTED]> 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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Subqueries

[Pascal Tufenkji]

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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Helio Campos Mello de Andrade
Sent: Thursday, October 30, 2008 2:53 PM
To: [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.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 <[EMAIL PROTECTED]>
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: [SQL] Subqueries

[Helio Campos Mello de Andrade]

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 <[EMAIL PROTECTED]>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: [SQL] Subqueries

[Oliveiros Cristina]

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

   

   

   

Re: [SQL] Subqueries returning more than one value?

[CoL]

hi,

Adam Witney wrote, On 5/11/2004 02:09:
Hi,

I am using a function in a subquery, this works ok:

SELECT name, (SELECT p_my_func(1)) AS id
  FROM test;
However I would like to have the function return 2 values into the main
query... Something like this:
SELECT name, (SELECT p_my_func(1)) AS (id, type)
  FROM test;
Of course this gives the error:

ERROR:  subquery must return only one column

Is there any way around this?
SELECT name,t.*  FROM (SELECT p_my_func(1)) AS t(id, type), test

C.

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Subqueries in select clause

[Tom Lane]

"Josh Berkus" <[EMAIL PROTECTED]> writes:
> Until you can upgrade, though, try this approach:

> CREATE VIEW c_aggregate AS
> SELECT sum(a) as sum_a, b FROM c GROUP BY b;

> SELECT max(sum_a) FROM c_aggregate;

Unfortunately that won't work in 7.0 --- grouped views have a lot of
problems in that version, and one of the problems is that you can't
do another level of aggregating on their results.

Basically a view and a subselect are the same thing, so you can't get
around the restrictions of one by using the other...

7.1 is what Sara needs.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Subqueries in select clause

[Josh Berkus]

Sara,

Hey!   Great to see that Postgres has made it to Israel.  What's the
most popular Linux distribution there?

I think you have your answer ... an upgrade.  RPMs for most major
distributions of Linux should be available within the week.

Until you can upgrade, though, try this approach:

CREATE VIEW c_aggregate AS
SELECT sum(a) as sum_a, b FROM c GROUP BY b;

SELECT max(sum_a) FROM c_aggregate;

Not as fast or dynamic as a subselect, but should solve your immediate
problem.

-Josh Berkus


__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] Subqueries in select clause

[Richard Huxton]

Sara Cohen <[EMAIL PROTECTED]> said:

> Hi,
> 
> I am attempting to use subqueries in the select clause of a query
> and am encountering difficulties.
> 
> The Problem:
> 
> 
> I would like to use a subquery that returns one column, but more than one
> tuple. The result of this subquery is then used in an aggregate function.

> Using Oracle, I could get by this problem with:
> 
>   select max(d) from ((select count(b) as d from c group by a));
> 
> However, my version of postgres doesn't support subqueries in the from
> clause.

In 7.1 at least you can do it if you alias the sub-query:

select max(d) from (select count(b) as d from c group by a) as calias;

Sorry, I don't know if 7.0.2 handles this (but you probably want to upgrade
anyway - I seem to remember some problems with 7.0.2)

HTH

- Richard Huxton

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] Subqueries in select clause

[Stephan Szabo]

On Wed, 18 Apr 2001, Sara Cohen wrote:

> The Problem:
> 
> 
> I would like to use a subquery that returns one column, but more than one
> tuple. The result of this subquery is then used in an aggregate function.
> 
> For example, suppose I have a table c, with columns a and b of
> numbers. Then I would like to be able to write something of the style:
> 
>   select max((select count(b) from c group by a));
> 
> However, when I try it, I get an error message:
> 
> ERROR:  More than one tuple returned by a subselect used as an expression.

Yeah, technically I think the spec (at least my sql92 draft) guards
against this by saying that the set functions can't take set functions
or subqueries.

> I actually need to use this type of construct in many cases, some of which
> have subqueries using values that appear in the outer query (i.e.,
> correlated subqueries). Thus, it would be difficult for me to simply
> create a temporary table with the value of the inner query and then use it
> to solve the query I need. (Since I need to translate automatically from
> queries with the above type of constructs to something that will run on
> Postgresql.)
> 
> Using Oracle, I could get by this problem with:
> 
>   select max(d) from ((select count(b) as d from c group by a));

Upgrade to 7.1 :)

Actually, technically for postgres it'll be:
 select max(d) from (select count(b) as d from c group by a) e;
It enforces the requirement of naming the subqueries.

However in 7.0, you *might* be able to do something like:
select count(b) as d from c group by a order by 1 desc limit 1;



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] Subqueries in select clause

[Tom Lane]

Sara Cohen <[EMAIL PROTECTED]> writes:
> Using Oracle, I could get by this problem with:
>   select max(d) from ((select count(b) as d from c group by a));
> However, my version of postgres doesn't support subqueries in the from
> clause.

Time to update to 7.1...

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] Subqueries in Non-SELECT Queries

[Frank Joerdens]

Christof Glaser wrote:
[ . . . ]
> since the where clause compares just one field for equality. You might
> try IN instead of =, ie
> 
> update index set level = 2 where parentid in ( select id from
> index where level = 1 );

That was it! Thanks, Frank

Re: [SQL] Subqueries in Non-SELECT Queries

[Tom Lane]

Frank Joerdens <[EMAIL PROTECTED]> writes:
> mpi=# update index set level = 2 where parentid = ( select id from
> index where level = 1 );
> ERROR:  More than one tuple returned by a subselect used as an expression.

Apparently the subquery "select id from index where level = 1" is
returning more than one row.  Perhaps you want WHERE parentid IN subselect
rather than WHERE parentid = subselect.  "=" is a scalar operator, not
a set operator.

regards, tom lane

Re: [SQL] Subqueries in Non-SELECT Queries

[Christof Glaser]

Frank Joerdens wrote:

> mpi=# update index set level = 2 where parentid = ( select id from
> index where level = 1 );
> ERROR:  More than one tuple returned by a subselect used as an
> expression. mpi=#
>
> This is a recursive query, on a single table. Is that not possible?

Hi Frank,

as the error message says, the subselect must return only one value,
since the where clause compares just one field for equality. You might 
try IN instead of =, ie

update index set level = 2 where parentid in ( select id from
index where level = 1 );

Hope that helps.

Christof
-- 
  gl.aser . software engineering . internet service
   http://gl.aser.de/  . Planckstraße 7 . D-39104 Magdeburg
Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3

RE: [SQL] subqueries as values in updates

[Francis Solomon]

Hi,

The syntax you used works fine for me.

francis=# select version();
version
---
 PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2
(1 row)

Hope this helps

Francis Solomon

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of
> [EMAIL PROTECTED]
> Sent: 08 December 2000 13:00
> To: liste SQL
> Subject: [SQL] subqueries as values in updates
>
>
> hello, list.
> are subqueries alloved as values in an update command ?
> e.g.
>
> update a set a.attribute1 = a.attribute1 +
> (select sum(b.attribute1) from b where b.attribute2=a.attribute2);
>
> If yes, how is the correct syntax ?
> If not, is there a hope for it in a future version ?

Re: [SQL] Subqueries in from clause

[Tom Lane]

Pierre Habraken <[EMAIL PROTECTED]> writes:
> It looks like if subqueries in from clause are not supported by
> PostgreSQL. Am I right ? If yes, are there any plans to provide this
> feature soon ?

Already there in current sources for 7.1 ...

regards, tom lane

Re: [SQL] Subqueries in from clause?

[Tom Lane]

Meszaros Attila <[EMAIL PROTECTED]> writes:
> How far is the above subject from beeing implemented?

I'm looking at it right now ... no promises yet, though.

regards, tom lane