Обсуждение: Getting one row for each subquery row...?
adding comments to a tableHi All,
here are my three tables. I wold like to list them in the following way: I
need all the columns from t_stockchanges, and one field for the pgoductgroup
the t_stockchanges.productid belongs to.
But one product can belong to many group, so i would need one row for each
group for each product.
My bad query is:
------------------------
DB=# 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;
ERROR: More than one tuple returned by a subselect used as an expression.
DB=#
---------------
Yes, this is absolutelly true, but I would like postgres to give me all the
tuples found. How can I ask him to do so?
Thank you,
-- Csaba
----------------------------------------------------------------------------
----------------------------------------------------
Table "public.t_stockchanges"
Column | Type | Modifiers
---------------+------------------+-----------------------------------------
------------
id | integer | not null
stockid | integer | not null
productid | integer | not null
changeid | integer | not null
quantity | double precision | not null
date | character(19) | not null
purchaseprice | double precision | not null
correction | double precision | not null
userid | integer | not null
time | character(19) | default to_char(now(), 'YYYY.mm.dd
hh:mi:ss'::text)
prooftype | character(10) | not null default ''
proofid | integer | default 0
Indexes: t_stockchanges_pkey primary key btree (id),
t_stockchanges_date btree (date),
t_stockchanges_productid btree (productid)
----------------------------------------------------------------------------
----------------------------------------------------
Table "public.t_productgroups"
Column | Type | Modifiers
-------------+-----------------------+-----------
id | integer | not null
name | character varying(30) | not null
description | character varying |
root | boolean |
Indexes: t_productgroups_pkey primary key btree (id)
----------------------------------------------------------------------------
----------------------------------------------------
Table "public.t_prod_in_pgr"
Column | Type | Modifiers
----------------+---------+-----------
productgroupid | integer | not null
productid | integer | not null
----------------------------------------------------------------------------
----------------------------------------------------
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.489 / Virus Database: 288 - Release Date: 2003. 06. 10.
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 assumption
thata 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'!
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
[Damn, didn't notice the lack of list address in the headers until I'd already sent this reply. The OP's address is elsewhere in the thread, obviously.] On Fri, 20 Jun 2003, Együd Csaba wrote: > Thank you Nigel, > > > > > > > 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; > > it also gives the same result as the solution I recevived from the pgsql-sql > list: > > (By Tomasz Myrta) --------------------------------------- > select > t_stockchanges.productid, > t_productgroups.name as pgroup > from > t_stockchanges > join t_prod_in using (productid) > join t_productgroups on (id=productgroupid) > ------------------------------------------------------------ > > The only modification I made is the use of distinct clause to avoid the > repeated rows. The point about the referential integrity and other constraints, like uniqueness is that: only with my assumption and not knowledge of what your db is doing I personnally would not have expected that query to give any duplicate rows. I would only expect to see duplicate rows if there are duplicate rows in one or more of your tables. Using foreign keys (which require uniqueness in the referenced table) would help you avoid such inconsistencies. For example in your t_prod_in_pgrp table how are you preventing: productid | productgroupid ---------------------------- 234 | 24 234 | 24 234 | 24 which in the query I gave would give you three rows the same? Equally, how are you preventing in t_productgroups: id | col1 ... ------------... 24 | ... 24 | ... which when combined with the first 'anomoly' would give you six rows the same from that query? BTW, the above query while the same as my version (I haven't inspected it so I take your word for it) is not so good. That is forcing postgresql to join in the tables in the specified order. Using the join constraints in the where clause as mine does lets the planner choose what it thinks is the best join order. -- Nigel J. Andrews