Обсуждение: select on many-to-many relationship
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.
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_productGROUP BY l.id_project; -- Victor Y. Yegorov
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