Re: Return t/f on existence of a join

On Sep 21, 2007, at 2:17 PM, Madison Kelly wrote:

> ... Or something like that. :)
>
> Sorry for so many questions! I have another "how do I create this
> query?" question, if it's okay.
>
> I've got three tables; 'foo', 'bar' and 'baz'.
>
> In 'foo' I've got 'foo_id' which is a PK. I've also got a bunch of
> other info, but in essence this is the "parent" table that all others
> reference in some way.
>
> In 'bar' I've got 'bar_id' which is also a PK. I also have
> 'bar_foo_id' which is a FK pointing to 'foo' -> 'foo_id', to show what
> 'foo' row it (primarily) belongs to.
>
> Lastly, I've got a table called 'baz' which has 'baz_id'. In it,
> there
> are just two columns;
>
> - 'baz_foo_id' which is a FK pointing to 'foo' -> 'foo_id'.
> - 'baz_bar_id' which is a FK pointing to 'bar' -> 'bar_id'.
>
> This last table, 'baz' is used as a way for saying 'bar *also*
> belongs
> to a given 'foo' row,
>
> So now my question;
>
> 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?
>
> I hope this isn't too muddy. I think part of my problem is I am
> having
> trouble even visualizing my question...
>
> Thanks as always!
>
> Madi

*Not tested* If this isn't quite it, then it should at least get you
close

SELECT foo.foo_id, CASE WHEN baz.foo_id IS NULL THEN FALSE ELSE TRUE
as has_baz
FROM foo
LEFT JOIN baz ON (foo.foo_id=baz.baz_foo_id AND baz.baz_bar_id=$bar_id)

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com