pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting all rows even if not a member of any groups
5+ messages / 2 participants
[nested] [flat]
* Getting all rows even if not a member of any groups
@ 2003-06-27 11:13 Együd Csaba <csegyud@freemail.hu>
0 siblings, 1 reply; 5+ messages in thread
From: Együd Csaba @ 2003-06-27 11:13 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a product table identified by its id field. There is a productgroups
table with productisd, productgroupid fields. And I have a prod_in_pgr
(productid, productgroupid) table which describes the membership of
productgroups. Each product can be a member of zero or more productgroups,
but one productgroup can contain a product only once.
I would like to list the following information:
productgroupid | productid | ... some other prouduct info | ...
I need all the products even if it is not a member in any productgroups. I
need these information ordered by productgroup and then productid.
--------------------------------
An example:
select t_productgroups.name as pgroup,
t_products.id as productid
from t_products
join t_prod_in_pgr on (t_products.id=productid)
join t_productgroups on (t_productgroups.id = t_prod_in_pgr.productgroupid)
order by pgroup, productid;
-------------------->
pgroup | productid
----------------------+-----------
Alumínium profilok | 6047
Alumínium profilok | 6048
Alumínium profilok | 6049
Alumínium profilok | 6050
Alumínium profilok | 6051
Alumínium profilok | 6052
Alumínium profilok | 6053
Alumínium profilok | 6054
Alumínium profilok | 6055
Alumínium profilok | 6056
Alumínium profilok | 6057
Alumínium profilok | 6058
Alumínium profilok | 6059
Alumínium profilok | 6060
Alumínium profilok | 6061
Alumínium profilok | 6062
Gumik | 6063
Hohíd mentes profilok | 6060
Hohíd mentes profilok | 6061
Hohíd mentes profilok | 6062
Hohidas profilok | 6050
Hohidas profilok | 6051
Hohidas profilok | 6052
Hohidas profilok | 6053
Hohidas profilok | 6054
Hohidas profilok | 6055
Hohidas profilok | 6056
Hohidas profilok | 6057
Hohidas profilok | 6058
Hohidas profilok | 6059
Nyílászárók | 6064
--------------------------------------------------------
I hope it is understandable. This query is a result of a 'join'-ed query
(see above), but it can contain only those products which are in one or more
groups. But I also need the ungroupd items.
Pleas tell me how to create such an sql query.
Thank you,
-- Csaba
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting all rows even if not a member of any groups
@ 2003-06-27 14:08 Bruno Wolff III <bruno@wolff.to>
parent: Együd Csaba <csegyud@freemail.hu>
0 siblings, 1 reply; 5+ messages in thread
From: Bruno Wolff III @ 2003-06-27 14:08 UTC (permalink / raw)
To: Együd Csaba <csegyud@freemail.hu>; +Cc: pgsql-sql
On Fri, Jun 27, 2003 at 13:13:07 +0200,
Együd Csaba <csegyud@freemail.hu> wrote:
> Hi,
Please don't reply to messages to start a new thread.
> I have a product table identified by its id field. There is a productgroups
> table with productisd, productgroupid fields. And I have a prod_in_pgr
> (productid, productgroupid) table which describes the membership of
> productgroups. Each product can be a member of zero or more productgroups,
> but one productgroup can contain a product only once.
>
> I would like to list the following information:
> productgroupid | productid | ... some other prouduct info | ...
>
> I need all the products even if it is not a member in any productgroups. I
> need these information ordered by productgroup and then productid.
> --------------------------------
> An example:
>
> select t_productgroups.name as pgroup,
> t_products.id as productid
> from t_products
> join t_prod_in_pgr on (t_products.id=productid)
> join t_productgroups on (t_productgroups.id = t_prod_in_pgr.productgroupid)
> order by pgroup, productid;
I think you want something like:
select t_productgroups.name as pgroup,
t_products.id as productid
from t_products
left join (t_prod_in_pgr
join t_productgroups on (t_productgroups.id = t_prod_in_pgr.productgroupid))
on (t_products.id=productid)
order by pgroup, productid;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting all rows even if not a member of any groups
@ 2003-06-27 14:18 Együd Csaba <csegyud@freemail.hu>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 5+ messages in thread
From: Együd Csaba @ 2003-06-27 14:18 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
Bruno,
> Please don't reply to messages to start a new thread.
sorry, I will never do such things in the future.
> select t_productgroups.name as pgroup,
> t_products.id as productid
> from t_products
> left join (t_prod_in_pgr
> join t_productgroups on (t_productgroups.id =
t_prod_in_pgr.productgroupid))
> on (t_products.id=productid)
> order by pgroup, productid;
This is absolutelly what I want, but I can't understand how it is working.
Where can I find a descriptive (tale-like, for kids ... :) ) documentation
about using joins?
Thank you wery mauch.
-- Csaba
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting all rows even if not a member of any groups
@ 2003-06-27 15:10 Bruno Wolff III <bruno@wolff.to>
parent: Együd Csaba <csegyud@freemail.hu>
0 siblings, 1 reply; 5+ messages in thread
From: Bruno Wolff III @ 2003-06-27 15:10 UTC (permalink / raw)
To: Együd Csaba <csegyud@freemail.hu>; +Cc: pgsql-sql
On Fri, Jun 27, 2003 at 16:18:10 +0200,
Együd Csaba <csegyud@freemail.hu> wrote:
>
> This is absolutelly what I want, but I can't understand how it is working.
> Where can I find a descriptive (tale-like, for kids ... :) ) documentation
> about using joins?
If you look at the documentation for the select command and page down a
bit there is a description of join syntax. Note that in 7.4 using
the explicit join syntax won't force join order. (This really only
affects cross joins and inner joins; left and right joins normally
can't be reordered.)
You need a left join to pick up products that aren't in any group.
The parenthesis changed the join order so that group names were attached
to group ids before group ids were joined to products. This can have
performance implications. I think that this is probably the faster
way, but the other option would to have been to make the second join
a left join as well.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting all rows even if not a member of any groups
@ 2003-07-02 10:00 Együd Csaba <csegyud@freemail.hu>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 5+ messages in thread
From: Együd Csaba @ 2003-07-02 10:00 UTC (permalink / raw)
To: pgsql-sql
Dear All,
thank you for your help, it was really efficient.
I'll get by with it now. Thanks.
-- Csaba
----- Original Message -----
From: "Bruno Wolff III" <bruno@wolff.to>
To: "Együd Csaba" <csegyud@freemail.hu>
Cc: <pgsql-sql@postgresql.org>
Sent: Friday, June 27, 2003 5:10 PM
Subject: Re: [SQL] Getting all rows even if not a member of any groups
> On Fri, Jun 27, 2003 at 16:18:10 +0200,
> Együd Csaba <csegyud@freemail.hu> wrote:
> >
> > This is absolutelly what I want, but I can't understand how it is
working.
> > Where can I find a descriptive (tale-like, for kids ... :) )
documentation
> > about using joins?
>
> If you look at the documentation for the select command and page down a
> bit there is a description of join syntax. Note that in 7.4 using
> the explicit join syntax won't force join order. (This really only
> affects cross joins and inner joins; left and right joins normally
> can't be reordered.)
>
> You need a left join to pick up products that aren't in any group.
> The parenthesis changed the join order so that group names were attached
> to group ids before group ids were joined to products. This can have
> performance implications. I think that this is probably the faster
> way, but the other option would to have been to make the second join
> a left join as well.
>
>
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-07-02 10:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-27 11:13 Getting all rows even if not a member of any groups Együd Csaba <csegyud@freemail.hu>
2003-06-27 14:08 ` Bruno Wolff III <bruno@wolff.to>
2003-06-27 14:18 ` Együd Csaba <csegyud@freemail.hu>
2003-06-27 15:10 ` Bruno Wolff III <bruno@wolff.to>
2003-07-02 10:00 ` Együd Csaba <csegyud@freemail.hu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox