Re: It it possible to get this result in one query?

Sure:

select
    t3.id,
    coalesce
       (
       t1.title,
       t2.title,
       t3.title
       ),
    coalesce
       (
       case
          when t1.title is not null
          then 'table_one,'
          else null
       end,
       case
          when t2.title is not null
          then 'table_two,'
          else null
       end,
       ''
       ) || 'table_three'
from
    table_three t3
    left outer join table_two t2 using (id)
    left outer join table_one t1 using (id)

On 10/14/2010 8:13 PM, Nick wrote:
> I guess I should mention that im basically searching for a way to
> recusively coalesce the title. So I want to search the second table
> and
>
> table_one (id,title)
> 1 | new one
>
> table_two (id,title)
> 2 | new two
>
> table_three (id,title)
> 1 | one
> 2 | two
> 3 | three
>
> Id like an sql statement that returns...
> 1 | new one | [table_one,table_three]
> 2 | new two | [table_two,table_three]
> 3 | three | [table_three]
>
>
> On Oct 14, 4:49 pm, Nick<nboutel...@gmail.com>  wrote:
>> Is it possible to get the results of this snip of a function without
>> using a function? All tables include an id and title column.
>>
>> tables := ARRAY[table_one,table_two,table_three]::VARCHAR;
>> CREATE TEMP TABLE final_results (id INTEGER, title VARCHAR, r_types
>> VARCHAR[]);
>> FOR t IN ARRAY_LOWER(tables,1) .. ARRAY_UPPER(tables,1) LOOP
>>    FOR r IN EXECUTE 'SELECT id, title FROM ' || tables[t] LOOP
>>      IF (SELECT TRUE FROM final_results WHERE id = r.id LIMIT 1) THEN
>>        UPDATE final_results SET r_types =
>> array_append(r_types,tables[t]) WHERE id = r.id;
>>      ELSE
>>        INSERT INTO final_results (id,title,r_types) VALUES
>> (r.id,r.title,ARRAY[tables.t]);
>>    END LOOP;
>> END LOOP;
>
>


--
Guy Rouillier