Hmmm.. I see where you're going with this... there is a possibility there.

Where it may fail is if I take away the WHERE clause (i.e. return this
pair for all records in the main table). I think I would only get back
the first record.

--- In firebird-support@yahoogroups.com, Robert martin <rob@c...> wrote:
> You could do it with a LEFT JOIN
>
> i.e
>
> SELECT FIRST 1 a.Desc, b.ID2
> FROM a
> LEFT JOIN b ON b.ID1 = a.ID1
> WHERE a.ID1 = :KeyValue
>
>
> In your code a test for NULL on the b.ID2 field tells you if there are
> any records in 'b'. I don't know which method gives better performance
> but in your case I prefer your original method.
>
> Rob Martin
> Software Engineer
>
> phone +64 03 377 0495
> fax +64 03 377 0496
> web www.chreos.com
>
> Wild Software Ltd
>
>
>
> 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
> >
> >
> >
> >
> >
> >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> >Visit http://firebird.sourceforge.net and click the Resources item
> >on the main (top) menu. Try Knowledgebase and FAQ links !
> >
> >Also search the knowledgebases at http://www.ibphoenix.com
> >
> >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> >Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
> >
> >