From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Cartesian product not correct |
Date: | 2009-09-14 14:13:37 |
Message-ID: | 20090914141337.GC12722@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
In response to Angus Miller :
> Hi
>
> Given the sql below shouldn't I get the 3 records back?
No, why?
>
> create table t1 (attribute text);
> insert into t1 values ('cars');
> insert into t1 values ('trucks');
> insert into t1 values ('bikes');
>
> create table t2 (id serial, category text, attribute text, val integer);
> insert into t2(category, attribute, val) values ('vehicle','cars', 100);
>
> select t2.category, t1.attribute, 0 as val
> from t2, t1
> where t2.id in (
> select max(id)
> from t2
> group by category
> );
The inner select returns:
test=*# select max(id) from t2 group by category;
max
-----
1
(1 row)
The outer query contains:
where t2.id in ( result from the inner select )
You have only one record in t2, with id = 1.
Maybe you want:
test=*# select t2.category, t1.attribute, 0 as val from t2, t1
test-# ;
category | attribute | val
----------+-----------+-----
vehicle | cars | 0
vehicle | trucks | 0
vehicle | bikes | 0
(3 rows)
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-09-14 14:13:45 | Re: postgresql.key secure storage |
Previous Message | Tom Lane | 2009-09-14 14:10:45 | Re: Cartesian product not correct |