Received: from comptechnews.com (cc993546-b.srst1.fl.home.com [24.3.77.52]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0970vB86220 for ; Tue, 9 Jan 2001 02:00:58 -0500 (EST) (envelope-from reaster@comptechnews.com) Received: from comptechnews (reaster@localhost [127.0.0.1]) by comptechnews.com (8.10.2/8.10.2) with SMTP id f0976UZ32603 for ; Tue, 9 Jan 2001 02:06:30 -0500 From: "Robert B. Easter" To: pgsql-sql@postgresql.org Subject: subselect test Date: Tue, 9 Jan 2001 02:06:30 -0500 X-Mailer: KMail [version 1.1.99] Content-Type: text/plain; charset="iso-8859-1" MIME-Version: 1.0 Message-Id: <0101090206301S.09559@comptechnews> Content-Transfer-Encoding: 8bit X-Archive-Number: 200101/83 X-Sequence-Number: 744 SELECT a, (SELECT b FROM y LEFT JOIN n ON b = c WHERE b = a + 1) AS b, c FROM (SELECT * FROM y CROSS JOIN (x NATURAL RIGHT JOIN n)) AS z NATURAL JOIN (SELECT * FROM x) AS zz WHERE a NOT IN (SELECT b FROM y NATURAL JOIN x WHERE b > 5) GROUP BY a, b, c HAVING a IN (SELECT b FROM y LEFT JOIN x ON (a = b) CROSS JOIN n WHERE b > 1) INTERSECT SELECT * FROM x,y,n EXCEPT SELECT * FROM x,y,n WHERE a < 3 ORDER BY a DESC LIMIT 10; a | b | c ----------+----------+---------- 5.000000 | 6.000000 | 1.000000 5.000000 | 6.000000 | 2.000000 5.000000 | 6.000000 | 3.000000 5.000000 | 6.000000 | 4.000000 5.000000 | 6.000000 | 5.000000 5.000000 | 6.000000 | 6.000000 5.000000 | 6.000000 | 7.000000 4.000000 | 5.000000 | 1.000000 4.000000 | 5.000000 | 2.000000 4.000000 | 5.000000 | 3.000000 (10 rows) Is this confusing? Is the result correct? Anyway, these new complex queries seem to be doing something in 7.1 cvs! -- -------- Robert B. Easter reaster@comptechnews.com --------- -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- ---------- http://www.comptechnews.com/~reaster/ ------------