Re: Getting one row for each subquery row...?

On Thu, 19 Jun 2003, Dennis Gearon wrote:

> First off, I've nerver heard of using Floating point for money. There
> **IS** rounding and truncation that goes on, in even double precision.
> The bean counters get upset if you lose .0000001 dollars, especially if
> it occurs for several million transactions. The usual choice is DECIMAL.
>
> And I'd use some sort of DATE or TIMESTAMP for the date. and time values.
>
> since you didn't supply any notes, it would be easier to understand if
> you used foreign key constraints, and a sounder design perhaps.
>
> And so that I can understand my own queries, I indent them etc(postgres
> cares not):
>
> select
>     t_stockchanges.productid,
>     (select
>         name from t_productgroups
>     where id=
>         (select
>            productgroupid from t_prod_in_pgr
>         where productid=t_stockchanges.productid
>         )
>     )
> as pgroup
> from t_stockchanges;
>
> From you table definitions and titles, I assume, 't_prod_in_pgr' is 'product in process'. You are making an
assumptionthat a user (?) web user (?) can only have on line entered in that table at a time? 
>
> You will have to explain more about this for me to get it, perhaps for others you will not.
>
> Also, it seems to me that 'productgroupid' should be related to 't_productgroups', but I can not tell.
>
> Tell us more, it gets 'curiouser and curiouser'!
>

Having said that I'm sure you've worked out the intention is probably:

select t_stockchanges.productid, t_productgroups.name
  from t_stockchanges, t_productgroup, t_prod_in_pgr
  where t_stockchanges.productid = t_prod_in_pgr.productid
     and t_productgroups.id = t_prod_in_pgr.productgroupid
;

Without the foreign keys and other constraints though that could get very
wrong as you pointed out Dennis.


--
Nigel J. Andrews