Re: Cartesian product not correct

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)