Обсуждение: Return t/f on existence of a join

Поиск
Список
Период
Сортировка

Return t/f on existence of a join

От
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

Re: Return t/f on existence of a join

От
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®
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

Solved! Was (Return t/f on existence of a join)

От
Madison Kelly
Дата:
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

Re: Return t/f on existence of a join

От
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/

Wait, not solved... Was (Return t/f on existence of a join)

От
Madison Kelly
Дата:
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

Actually Solved! Was: (Return t/f on existence of a join)

От
Madison Kelly
Дата:
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