Return t/f on existence of a join

From: Madison Kelly <linux(at)alteeve(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Return t/f on existence of a join
Date: 2007-09-21 19:17:56
Message-ID: 46F418E4.8010406@alteeve.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Erik Jones 2007-09-21 19:32:48 Re: Return t/f on existence of a join
Previous Message Rhys Stewart 2007-09-21 18:29:07 set returning functions.