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(dot)schwaighofer(at)tequila(dot)jp> wrote:

> From: Schwaighofer Clemens <clemens(dot)schwaighofer(at)tequila(dot)jp>
> Subject: [GENERAL] Query Question
> To: pgsql-general(at)postgresql(dot)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(at)tbwaworld(dot)com(dot) 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(at)postgresql(dot)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.