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