JOINing subselects in FROM? 
Author Message
 JOINing subselects in FROM?

Hello:

I'm playing around with the syntax of the SELECT.

I have a very simple table (this is a silly example):

create table datos (a?o int, mes text, valor int);

with a couple tuples:

insert into datos (a?o, mes, valor) values (1999, 'enero', 100);
insert into datos (a?o, mes, valor) values (2000, 'enero', 200);
insert into datos (a?o, mes, valor) values (1999, 'febrero', 300);

I'm trying to JOIN two subselects-in-from clauses and can't get it
right:

SELECT foo1.mes, valor1999 AS "1999", valor2000 AS "2000" FROM
        (SELECT valor AS valor1999, mes FROM datos WHERE a?o=1999) AS foo1
    OUTER JOIN
        (SELECT valor AS valor2000, mes FROM datos WHERE a?o=2000) AS foo2
    ON foo1.mes=foo2.mes;
ERROR:  parser: parse error at or near "OUTER"

If I try to do an INNER JOIN, I get instead
SELECT foo1.mes, valor1999 AS "1999", valor2000 AS "2000" FROM
        (SELECT valor AS valor1999, mes FROM datos WHERE a?o=1999) AS foo1
    INNER JOIN
        (SELECT valor AS valor2000, mes FROM datos WHERE a?o=2000) AS foo2
    ON foo1.mes=foo2.mes;
ERROR:  flatten_join_alias_var: unexpected subtree type

But if I do it like
SELECT foo1.mes, valor1999 AS "1999", valor2000 AS "2000" FROM
        (SELECT valor AS valor1999, mes FROM datos WHERE a?o=1999) AS foo1,
        (SELECT valor AS valor2000, mes FROM datos WHERE a?o=2000) AS foo2
  WHERE foo1.mes=foo2.mes;

it works (but I lose some tuples -- I expected to get NULL there in the
OUTER JOIN query).

Remember, this is a silly example, I'm not trying to use it for anything
but playing around with the syntax. But I wonder whether this is
supposed to work?

alvherre=> select version();
                            version                            
----------------------------------------------------------------
 PostgreSQL 7.3devel on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

On a related note, psql's "\h select" shows the syntax for select, but
it doesn't specify what are the terminal symbols for the join_type
non-terminal.

(this is a week-old or so CVS)

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente"

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Sun, 26 Sep 2004 13:31:39 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Query help required: using inner joins and subselects

2. Bad results on QRY w/Subselects and a JOIN (MSSQL 6.5)

3. SubSelect in join

4. Outer-join w/ a subselect?

5. Performance Inner Joins and SubSelects using IN clause

6. Outer-join a subselect?

7. Bad results on QRY w/Subselects and a JOIN (MSSQL 6.5)

8. Right Join / Hash Joins / Nested Joins

9. Explicit Temp Table Instead of SubSelect

10. Subselects or eguijoins

11. Is this possible ("Dynamic subselect")

12. Variables within a subselect


 
Powered by phpBB® Forum Software