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(dot)(dot)(dot)(at)gmail(dot)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