Search Postgresql Archives

Re: Query Question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

Attachment: signature.asc
Description: OpenPGP digital signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux