pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect on many-to-many relationship
3+ messages / 3 participants
[nested] [flat]
* select on many-to-many relationship
@ 2012-11-27 10:13 ssylla <stefansylla@gmx.de>
2012-11-27 17:48 ` Re: select on many-to-many relationship Виктор Егоров <vyegorov@gmail.com>
2012-11-28 00:27 ` Re: select on many-to-many relationship Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: ssylla @ 2012-11-27 10:13 UTC (permalink / raw)
To: pgsql-sql
Dear list,
assuming I have the following n:n relationship:
t1:
id_project
1
2
t2:
id_product
1
2
intermediary table:
t3
id_project|id_product
1|1
1|2
2|1
How can I create an output like this:
id_project|id_product1|id_product2
1|1|2
2|1|NULL
--
View this message in context: http://postgresql.1045698.n5.nabble.com/select-on-many-to-many-relationship-tp5733696.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: select on many-to-many relationship
2012-11-27 10:13 select on many-to-many relationship ssylla <stefansylla@gmx.de>
@ 2012-11-27 17:48 ` Виктор Егоров <vyegorov@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Виктор Егоров @ 2012-11-27 17:48 UTC (permalink / raw)
To: ssylla <stefansylla@gmx.de>; +Cc: pgsql-sql
2012/11/27 ssylla <stefansylla@gmx.de>:
> assuming I have the following n:n relationship:
>
> intermediary table:
> t3
> id_project|id_product
> 1|1
> 1|2
> 2|1
>
> How can I create an output like this:
> id_project|id_product1|id_product2
> 1|1|2
> 2|1|NULL
I'd said the sample is too simplified — not clear which id_product
should be picked if there're more then 2 exists.
I assumed the ones with smallest IDs.
-- this is just a sample source generator
WITH t3(id_project, id_product) AS (VALUES (1,1),(1,2),(2,1))
-- this is the query
SELECT l.id_project, min(l.id_product) id_product1, min(r.id_product)
id_product2
FROM t3 l
LEFT JOIN t3 r ON l.id_project=r.id_project AND l.id_product < r.id_product
GROUP BY l.id_project;
--
Victor Y. Yegorov
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: select on many-to-many relationship
2012-11-27 10:13 select on many-to-many relationship ssylla <stefansylla@gmx.de>
@ 2012-11-28 00:27 ` Sergey Konoplev <gray.ru@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Sergey Konoplev @ 2012-11-28 00:27 UTC (permalink / raw)
To: ssylla <stefansylla@gmx.de>; +Cc: pgsql-sql
On Tue, Nov 27, 2012 at 2:13 AM, ssylla <stefansylla@gmx.de> wrote:
> id_project|id_product
> 1|1
> 1|2
> 2|1
>
> How can I create an output like this:
> id_project|id_product1|id_product2
> 1|1|2
> 2|1|NULL
You can use the crostab() function from the tablefunc module
(http://www.postgresql.org/docs/9.2/static/tablefunc.html). It does
exactly what you need.
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/select-on-many-to-many-relationship-tp5733696.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp
Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-11-28 00:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-27 10:13 select on many-to-many relationship ssylla <stefansylla@gmx.de>
2012-11-27 17:48 ` Виктор Егоров <vyegorov@gmail.com>
2012-11-28 00:27 ` Sergey Konoplev <gray.ru@gmail.com>
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