Re: Query Question

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(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 -----=====:::::~ ]
[ 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 ]