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@xxxxxxxxxx> wrote: > From: Schwaighofer Clemens <clemens.schwaighofer@xxxxxxxxxx> > Subject: Query Question > To: pgsql-general@xxxxxxxxxxxxxx > 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@xxxxxxxxxxxxxx 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@xxxxxxxxxxxxxx) > 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general