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.

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 ]