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@myemma.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

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/

Thanks to both of you, Erik and Jon!

I had to tweak your two replies to get what I wanted (all 'foo' rows
returned, was only getting ones with a match in 'baz'). You two sent me
on the right path though and I was able to work out the rest using the
PgSQL docs on 'CASE' and 'JOIN'.

Here is the working query (where 'bar_id'=2):

SELECT
CASE z.baz_bar_id
WHEN 2
THEN TRUE
ELSE FALSE
END AS tf_col,
f.foo_id
FROM foo f
LEFT OUTER JOIN baz z
ON (f.foo_id=z.baz_foo_id)
LEFT OUTER JOIN bar b
ON (b.bar_id=z.baz_bar_id)
AND
b.bar_id=2;

Thanks kindly to both! I honestly didn't expect to work this out
before then end of the day. Cool!

Madi

Madison Kelly wrote:

Thanks to both of you, Erik and Jon!

I had to tweak your two replies to get what I wanted (all 'foo' rows
returned, was only getting ones with a match in 'baz'). You two sent me
on the right path though and I was able to work out the rest using the
PgSQL docs on 'CASE' and 'JOIN'.

Here is the working query (where 'bar_id'=2):

SELECT
CASE z.baz_bar_id
WHEN 2
THEN TRUE
ELSE FALSE
END AS tf_col,
f.foo_id
FROM foo f
LEFT OUTER JOIN baz z
ON (f.foo_id=z.baz_foo_id)
LEFT OUTER JOIN bar b
ON (b.bar_id=z.baz_bar_id)
AND
b.bar_id=2;

Thanks kindly to both! I honestly didn't expect to work this out
before then end of the day. Cool!

Madi

Doh!

It's returning a row from 'foo' for every entry in baz that has an
entry pointing to foo (possibly same problem with each pointer to an
entry in bar, not sure yet). The 'true/false' part is working though...

Back to reading. *sigh* :)

Madi

Madison Kelly wrote:

It's returning a row from 'foo' for every entry in baz that has an
entry pointing to foo (possibly same problem with each pointer to an
entry in bar, not sure yet). The 'true/false' part is working though...

Back to reading. *sigh* :)

Madi

I'm sorry for all the line noise, but I like to post my solutions "for
the record".

Reading up a bit more of the JOIN types I was finally able to get all
rows in 'foo' returned just once with a t/f depending if a given
baz_bar_id exists. The query is:

(Where '2' is the 'baz_bar_id' I am checking on)

SELECT
f.foo_id,
f.foo_name,
CASE z.baz_foo_id
WHEN 2
THEN TRUE
ELSE FALSE
END
FROM foo f
LEFT JOIN baz z
ON f.foo_id=z.baz_foo_id
AND z.baz_bar_id=2;

Thanks again, both of you!

Madi