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)