[GENERAL] Return t/f on existence of a join

2007-09-21 Thread Madison Kelly 
... 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Return t/f on existence of a join

2007-09-21 Thread Erik Jones 

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®
[EMAIL PROTECTED]
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



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Return t/f on existence of a join

2007-09-21 Thread Jon Sime 
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/

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq