Re: Sv: how to build this query ??? Please help !!!

Syntax Rules

1) Each <column reference> in the <group by clause> shall
unambiguously reference a column of the table resulting from
the <from clause>. A column referenced in a <group by clause> is
a grouping column.

NOTE 101 - "Column reference" is defined in Subclause 6.6,
"<column reference>".

1) Every <column reference> has a qualifying table and a qualifying
scope, as defined in succeeding Syntax Rules.

The query I did was bad syntax I guess. That b only exists in the result
table, not in the table made in the FROM clause. Still, crashes so not good.

subselects=# select a, (SELECT b FROM y WHERE y.b = x.a + 1) from x WHERE a
IN (SELECT b FROM y) GROUP BY a, b HAVING a IN (SELECT b FROM y WHERE y.b >
1);
ERROR: Attribute 'b' not found
subselects=#

That's what should be expected (right?) and I get it if I remove "AS b".

On Monday 08 January 2001 17:24, Robert B. Easter wrote:
> I was playing around with subselects and managed to crash the backend (if
> you try, it can be done I guess):
>
> create table x (a numeric);
> create table y (b numeric);
>
> insert into x values (1);
> insert into x values (2);
> insert into x values (3);
> insert into x values (4);
> insert into x values (5);
> insert into x values (6);
> insert into x values (7);
> insert into x values (8);
> insert into x values (9);
> insert into x values (10);
> insert into x values (11);
>
> insert into y values (1);
> insert into y values (2);
> insert into y values (3);
> insert into y values (4);
> insert into y values (5);
> insert into y values (6);
> insert into y values (7);
> insert into y values (8);
> insert into y values (9);
>
> select a, (select b from y) from x;
> select a, (select b from y where b = a) from x;
>
> -- this is ok ...
> select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b
> FROM x WHERE a IN (SELECT b FROM y)
> GROUP BY a
> HAVING a IN (SELECT b FROM y WHERE b > 1);
> a | b
> ----------+----------
> 2.000000 | 3.000000
> 3.000000 | 4.000000
> 4.000000 | 5.000000
> 5.000000 | 6.000000
> 6.000000 | 7.000000
> 7.000000 | 8.000000
> 8.000000 | 9.000000
> 9.000000 |
> (8 rows)
>
> -- this crashes
> select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b
> FROM x WHERE a IN (SELECT b FROM y)
> GROUP BY a,b
> HAVING a IN (SELECT b FROM y WHERE b > 1);
>
> pqReadData() -- backend closed the channel unexpectedly.
> This probably means the backend terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !# \connect subselects
> No Postgres username specified in startup packet.
> !> \connect subselects pgcvs
> You are now connected to database subselects as user pgcvs.
> subselects=#
>
> This example is probably ridiculous and might break syntax!? Else maybe a
> bug in not liking "AS b".
>
> On Thursday 04 January 2001 06:13, Jens Hartwig wrote:
> > Hello Tom,
> >
> > > [...]
> > >
> > > > SELECT a, (SELECT b)
> > > > FROM xyz;
> > >
> > > [...]
> > > I think it's OK (we're assuming that a and b are columns of xyz,
> > > right?) [...]
> >
> > immediately after having sent my message I realized my fault: a and b
> > are not of the same table! Correctly, the statement had to be something
> > like:
> >
> > SELECT a, (SELECT b FROM z WHERE b = a)
> > FROM x;
> >
> > > [...]
> > > This is not really different from
> > > SELECT x FROM xyz WHERE y IN
> > > (SELECT a FROM abc WHERE b = xyz.z);
> > > [...]
> >
> > Now it is :-) In a subquery, the inner query is only used for things
> > like comparison (as it is in your example). In my example the result
> > shows me two columns (in one record!!) which belong to different tables.
> > Mmmmh ... I tested the following:
> >
> > create table x (a numeric);
> > create table y (b numeric);
> >
> > insert into x values (1);
> > insert into x values (2);
> >
> > insert into y values (1);
> > insert into y values (2);
> >
> > select a, (select b from y) from x;
> >
> > => ERROR: More than one tuple returned by a subselect used as an
> > expression.
> >
> > This is ok, anything else would have shocked me.
> >
> > select a, (select b from y where b = a) from x;
> >
> > a | ?column?
> > ----------+----------
> > 1.000000 | 1.000000
> > 2.000000 | 2.000000
> >
> > This result made me understanding that this special case of "subqueries"
> > is possibly nothing more than a special form of joins between tables:
> >
> > select a, b
> > from x, y
> > where x.a = y.b;
> >
> > brings the same result. Now, back to the first example (of Nikolaj):
> >
> > SELECT a_nr,
> > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder,
> > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
> > 'zdr') AS #zdr,
> > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
> > 'zcu') AS #zcu,
> > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
> > 'zcr') AS #zcr,
> > product, state
> > FROM orders;
> >
> > This would be a self-join of one table like:
> >
> > select ord.a_nr,
> > c1.count(*),
> > c2.count(*),
> > ...
> > from orders ord,
> > cylinders c1,
> > cylinders c2,
> > ...
> > where c1.z_a_nr = ord.a_nr
> > and c2.z_status = 'zdr'
> > and ...
> >
> > This in fact is not possible in PostgreSQL (it seems that the table
> > alias "c1" cannot be prefixed to the aggregate-function "count(*)") and
> > AFAIK in no other relational database. I really cannot imagine any
> > equivalent join-statement (or anything else like a subquery) which
> > brings the same results! Does this at all correlate with the philosophy
> > of a relational database?
> >
> > Best regards, Jens
> >
> > =============================================
> > Jens Hartwig
> > ---------------------------------------------
> > debis Systemhaus GEI mbH
> > 10875 Berlin
> > Tel. : +49 (0)30 2554-3282
> > Fax : +49 (0)30 2554-3187
> > Mobil : +49 (0)170 167-2648
> > E-Mail : jhartwig(at)debis(dot)com
> > =============================================

--
-------- Robert B. Easter reaster(at)comptechnews(dot)com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------