Re: Query Question

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.