Ah, yes the magical CASE statement. Yes this does work fine.

Thx much!

It is interesting that CASE WHEN EXISTS(...) ... works,
but simply EXISTS(...) doesn't (as part of the SELECT clause,
anyway. Seems like i'm opening a peanut with a sledgehammer, but
c'est la vie... this works fine.

Greg

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

> SELECT a.Desc,
> CASE
> WHEN EXISTS(SELECT b.ID2 from TABLE2 b where b.ID1=a.ID1)
> THEN 'I EXIST'
> ELSE 'I DO NOT THINK I AM HERE'
> END
> FROM TABLE1 a
> WHERE a.ID1 = <keyvalue>
>
> This works,
> Set
>
> --- In firebird-support@yahoogroups.com, "Greg At ACD" wrote:
> > Hi all,
> >
> > DB: FB 1.5.2 SuperServer
> >
> > I have 2 tables as follows (oversimplified for clarity):
> >
> > Table1:
> > ID1 int (this is a PK)
> > Desc varchar(80)
> >
> > Table2:
> > ID2 int (this is a PK)
> > ID1 int (this is a FK to Table 1)
> >
> > So, Table1 shares a one to many relationship with Table2.
> >
> > What I am trying to do is to create a query that, given a valid

key

> > value, returns "Desc" and an indicator as to whether or not

there

> > are any instances of ID1 in table 2.
> >
> > Something like this (obviously pseudo-type code here)
> > SELECT a.Desc,
> > EXISTS(SELECT b.ID2 from TABLE2 b where b.ID1=a.ID1) AS X
> > FROM TABLE1 a
> > WHERE a.ID1 = <keyvalue>
> >
> > I keep thinking there's a way of doing this in a single

statement,

> > but I cant seem to grab what it might be...
> >
> > thx!
> >
> > Greg