Received: from neptun.sns-felb.debis.de (neptun.sns-felb.debis.de [53.122.101.2]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f04BDi783396 for ; Thu, 4 Jan 2001 06:13:44 -0500 (EST) (envelope-from jhartwig@debis.com) Received: by neptun.sns-felb.debis.de; id MAA10286; Thu, 4 Jan 2001 12:18:49 +0100 Received: from unknown(53.113.82.10) by neptun.sns-felb.debis.de via smap (V5.0) id xma010282; Thu, 4 Jan 01 12:18:44 +0100 Received: from mail1.b3.dsh.de (localhost [127.0.0.1]) by dshmail1.dsh.de (8.9.1/8.8.7) with ESMTP id MAA05154; Thu, 4 Jan 2001 12:16:39 +0100 (MET) Received: from debis.com ([138.201.143.121]) by mail1.b3.dsh.de (8.10.0/8.10.0) with ESMTP id f04BDt513816; Thu, 4 Jan 2001 12:13:55 +0100 Message-ID: <3A545AF2.1C827233@debis.com> Date: Thu, 04 Jan 2001 12:13:54 +0100 From: Jens Hartwig Organization: debis Systemhaus GEI X-Mailer: Mozilla 4.7 [de] (WinNT; I) X-Accept-Language: de MIME-Version: 1.0 To: Tom Lane CC: Tod McQuillin , Nikolaj Lundsgaard , pgsql-sql@postgresql.org Subject: Re: Sv: how to build this query ??? Please help !!! References: <3A543766.B66B482C@debis.com> <21122.978601587@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/49 X-Sequence-Number: 710 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@debis.com =============================================