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
