Обсуждение: Query Question
I have two tables Table "public.mailings" Column | Type | Modifiers --------+-------------------+----------- key | character varying | name | character varying | Table "public.userdata" Column | Type | Modifiers --------+-------------------+----------- key | character varying | uid | character varying | name | character varying | which hold the following data mailing: key | name -----+-------- A1 | Test 1 A2 | Test 2 A3 | Test 3 A4 | Test 4 userdata: key | uid | name -----+-----+-------- A1 | B1 | Test 1 A3 | B1 | Test 3 A2 | B2 | Test 2 A3 | B2 | Test 3 A4 | B2 | Test 4 A2 | B2 | Test 2 A1 | B3 | Test 1 A4 | B3 | Test 4 A1 | B4 | Test 1 A2 | B5 | Test 2 A3 | B5 | Test 3 A4 | B5 | Test 4 A1 | B6 | Test 1 A2 | B6 | Test 2 A3 | B6 | Test 3 A4 | B6 | Test 4 I want to select the data between userdata and mailings, that adds me a null row to the mailings if mailing table does not have a matching row for "key" in the grouping "uid". So eg the result should look like this key | name | key | uid | name -----+--------+-----+-----+-------- A1 | Test 1 | A1 | B1 | Test 1 A2 | Test 2 | | | A3 | Test 3 | A3 | B1 | Test 3 A4 | Test 4 | | | A1 | Test 1 | | | A2 | Test 2 | A2 | B2 | Test 2 A2 | Test 2 | A2 | B2 | Test 2 A3 | Test 3 | A3 | B2 | Test 3 A4 | Test 4 | A4 | B2 | Test 4 ... but my problem is, that a normal join will not work, because both tables will hold a complete set of matching "key" data. I need to sub group the join through the "uid" column from the userdata. But i have no idea how to do this. Any idea if there is a simple way to do this? -- [ Clemens Schwaighofer -----=====:::::~ ] Advertising Age Global Agency of the Year 2008 Adweek Global Agency of the Year 2008 This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is strictly prohibited. All contents are the copyright property of TBWA Worldwide, its agencies or a client of such agencies. If you are not the intended recipient, you are nevertheless bound to respect the worldwide legal rights of TBWA Worldwide, its agencies and its clients. We require that unintended recipients delete the e-mail and destroy all electronic copies in their system, retaining no copies in any media.If you have received this e-mail in error, please immediately notify us via e-mail to disclaimer@tbwaworld.com. We appreciate your cooperation. We make no warranties as to the accuracy or completeness of this e-mail and accept no liability for its content or use. Any opinions expressed in this e-mail are those of the author and do not necessarily reflect the opinions of TBWA Worldwide or any of its agencies or affiliates.
Try working with this: SELECT m.key AS mailings_key, m.name AS mailings_name, COALESCE(u.key,'') AS userdata_key, COALESCE(u.uid,'') AS userdata_uid, COALESCE(u.name,'') AS userdata_name FROM (SELECT m0.key, m0.name, u0.uid FROM mailings m0, (SELECT DISTINCT uid FROM userdata) AS u0 ORDER BY u0.uid, m0.key) AS m LEFT OUTER JOIN userdata u ON u.key = m.key AND u.uid = m.uid ORDER BY m.uid, m.key Cheers, Ioana --- On Tue, 2/10/09, Schwaighofer Clemens <clemens.schwaighofer@tequila.jp> wrote: > From: Schwaighofer Clemens <clemens.schwaighofer@tequila.jp> > Subject: [GENERAL] Query Question > To: pgsql-general@postgresql.org > Received: Tuesday, February 10, 2009, 5:30 AM > I have two tables > > Table "public.mailings" > Column | Type | Modifiers > --------+-------------------+----------- > key | character varying | > name | character varying | > > Table "public.userdata" > Column | Type | Modifiers > --------+-------------------+----------- > key | character varying | > uid | character varying | > name | character varying | > > which hold the following data > > mailing: > > key | name > -----+-------- > A1 | Test 1 > A2 | Test 2 > A3 | Test 3 > A4 | Test 4 > > userdata: > > key | uid | name > -----+-----+-------- > A1 | B1 | Test 1 > A3 | B1 | Test 3 > A2 | B2 | Test 2 > A3 | B2 | Test 3 > A4 | B2 | Test 4 > A2 | B2 | Test 2 > A1 | B3 | Test 1 > A4 | B3 | Test 4 > A1 | B4 | Test 1 > A2 | B5 | Test 2 > A3 | B5 | Test 3 > A4 | B5 | Test 4 > A1 | B6 | Test 1 > A2 | B6 | Test 2 > A3 | B6 | Test 3 > A4 | B6 | Test 4 > > I want to select the data between userdata and mailings, > that adds me > a null row to the mailings if mailing table does not have a > matching > row for "key" in the grouping "uid". > > So eg the result should look like this > > key | name | key | uid | name > -----+--------+-----+-----+-------- > A1 | Test 1 | A1 | B1 | Test 1 > A2 | Test 2 | | | > A3 | Test 3 | A3 | B1 | Test 3 > A4 | Test 4 | | | > A1 | Test 1 | | | > A2 | Test 2 | A2 | B2 | Test 2 > A2 | Test 2 | A2 | B2 | Test 2 > A3 | Test 3 | A3 | B2 | Test 3 > A4 | Test 4 | A4 | B2 | Test 4 > ... > > but my problem is, that a normal join will not work, > because both > tables will hold a complete set of matching "key" > data. I need to sub > group the join through the "uid" column from the > userdata. > > But i have no idea how to do this. Any idea if there is a > simple way to do this? > > -- > [ Clemens Schwaighofer > -----=====:::::~ ] > > Advertising Age Global Agency of the Year 2008 > Adweek Global Agency of the Year 2008 > > This e-mail is intended only for the named person or entity > to which > it is addressed and contains valuable business information > that is > privileged, confidential and/or otherwise protected from > disclosure. > Dissemination, distribution or copying of this e-mail or > the > information herein by anyone other than the intended > recipient, or > an employee or agent responsible for delivering the message > to the > intended recipient, is strictly prohibited. All contents > are the > copyright property of TBWA Worldwide, its agencies or a > client of > such agencies. If you are not the intended recipient, you > are > nevertheless bound to respect the worldwide legal rights of > TBWA > Worldwide, its agencies and its clients. We require that > unintended > recipients delete the e-mail and destroy all electronic > copies in > their system, retaining no copies in any media.If you have > received > this e-mail in error, please immediately notify us via > e-mail to > disclaimer@tbwaworld.com. We appreciate your cooperation. > > We make no warranties as to the accuracy or completeness of > this > e-mail and accept no liability for its content or use. Any > opinions > expressed in this e-mail are those of the author and do not > > necessarily reflect the opinions of TBWA Worldwide or any > of its > agencies or affiliates. > > > -- > Sent via pgsql-general mailing list > (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general __________________________________________________________________ Yahoo! Canada Toolbar: Search from anywhere on the web, and bookmark your favourite sites. Download it now at http://ca.toolbar.yahoo.com.
On 02/11/2009 01:10 AM, Ioana Danes wrote: > Try working with this: > > SELECT m.key AS mailings_key, > m.name AS mailings_name, > COALESCE(u.key,'') AS userdata_key, > COALESCE(u.uid,'') AS userdata_uid, > COALESCE(u.name,'') AS userdata_name > FROM (SELECT m0.key, m0.name, u0.uid > FROM mailings m0, (SELECT DISTINCT uid FROM userdata) AS u0 > ORDER BY u0.uid, m0.key) AS m > LEFT OUTER JOIN userdata u ON u.key = m.key AND u.uid = m.uid > ORDER BY m.uid, m.key Great, this one works too! > Cheers, > Ioana > > --- On Tue, 2/10/09, Schwaighofer Clemens <clemens.schwaighofer@tequila.jp> wrote: > >> From: Schwaighofer Clemens <clemens.schwaighofer@tequila.jp> >> Subject: [GENERAL] Query Question >> To: pgsql-general@postgresql.org >> Received: Tuesday, February 10, 2009, 5:30 AM >> I have two tables >> >> Table "public.mailings" >> Column | Type | Modifiers >> --------+-------------------+----------- >> key | character varying | >> name | character varying | >> >> Table "public.userdata" >> Column | Type | Modifiers >> --------+-------------------+----------- >> key | character varying | >> uid | character varying | >> name | character varying | >> >> which hold the following data >> >> mailing: >> >> key | name >> -----+-------- >> A1 | Test 1 >> A2 | Test 2 >> A3 | Test 3 >> A4 | Test 4 >> >> userdata: >> >> key | uid | name >> -----+-----+-------- >> A1 | B1 | Test 1 >> A3 | B1 | Test 3 >> A2 | B2 | Test 2 >> A3 | B2 | Test 3 >> A4 | B2 | Test 4 >> A2 | B2 | Test 2 >> A1 | B3 | Test 1 >> A4 | B3 | Test 4 >> A1 | B4 | Test 1 >> A2 | B5 | Test 2 >> A3 | B5 | Test 3 >> A4 | B5 | Test 4 >> A1 | B6 | Test 1 >> A2 | B6 | Test 2 >> A3 | B6 | Test 3 >> A4 | B6 | Test 4 >> >> I want to select the data between userdata and mailings, >> that adds me >> a null row to the mailings if mailing table does not have a >> matching >> row for "key" in the grouping "uid". >> >> So eg the result should look like this >> >> key | name | key | uid | name >> -----+--------+-----+-----+-------- >> A1 | Test 1 | A1 | B1 | Test 1 >> A2 | Test 2 | | | >> A3 | Test 3 | A3 | B1 | Test 3 >> A4 | Test 4 | | | >> A1 | Test 1 | | | >> A2 | Test 2 | A2 | B2 | Test 2 >> A2 | Test 2 | A2 | B2 | Test 2 >> A3 | Test 3 | A3 | B2 | Test 3 >> A4 | Test 4 | A4 | B2 | Test 4 >> ... >> >> but my problem is, that a normal join will not work, >> because both >> tables will hold a complete set of matching "key" >> data. I need to sub >> group the join through the "uid" column from the >> userdata. >> >> But i have no idea how to do this. Any idea if there is a >> simple way to do this? -- [ Clemens Schwaighofer -----=====:::::~ ] [ IT Engineer/Web Producer/Planning/Manager ] [ E-Graphics Communications SP Digital ] [ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706 Fax: +81-(0)3-3545-7343 ] [ http://www.e-gra.co.jp ]