[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
