Re: Return t/f on existence of a join

Madison Kelly wrote:
> I want to create a query that will allow me to say "show me all 'foo'
> rows and tell me if a specific 'baz_id' belongs to it". Normally, I
> would do this:
>
> SELECT foo_id FROM foo;
> (for each returned row)
> {
> # Where '$foo_id' is the current 'foo_id' and '$bar_id' is
> # the specific/static 'bar_id' we are checking.
> SELECT COUNT(*) FROM baz z, bar r WHERE baz_foo_id=$foo_id AND
> baz_bar_id=$bar_id;
> ( if count > 0 ) { # TRUE } else { # FALSE }
> }
>
> This is pretty inefficient, obviously. How could I create a query that
> returned a TRUE/FALSE column that checks if there is a 'baz' record for
> a specified 'bar_id' in all 'foo_id's in one query?

What you want is a "left outer join" to do all of this in a single query.

select f.foo_id,
case when count(r.bar_id) > 0 then true else false end as tf_col
from foo f
join baz z on (z.baz_foo_id = f.foo_id)
left join bar r on (r.baz_bar_id = z.bar_id)
group by f.foo_id;

-Jon

--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/