Very nice solution. It works!

I took your ISNULL(LG.ID) to be a reference to any existing field in LG and replaced it with ISNULL(LG.kGarment) to give working code as follows:

select G.cStyle, G.kGarment, G.cDescrip, G.nDefPrice, IIF(ISNULL(LG.kGarment),'X','L') as cType ;
  from Garments G
LEFT JOIN LocGarment LG ON (LG.kGarment = G.kGarment) AND (LG.kLoc = lkLoc)

What you are doing is insisting on a result (LEFT JOIN), then evaluating the result as either "got one" or NULL. With this information, the cType flag gets set appropriately.

Thanks Naomi


>>Ok, let me think about getting the G. out. I still need a select though in my "real" code, rather than replacing the select with joins. The version that I offered was done to make the select easy to see (and generate an error).
>>
>>In the "real" code below:
>>

>>select G.cStyle, G.kGarment, G.cDescrip, G.nDefPrice, ;
>>       iif(lkLoc in ;
>>       (select LG.kLoc from LocGarment LG where LG.kGarment = G.kGarment), ;
>>       "L", "X") as cType  ;
>>  from Garments G
>>

>>... the error message is just that it needs a comma (I think that it's parsing the real error message and reporting on it). What I'm trying to do is to add and populate a calculated field (cType) so the subselect is located in the field list. I pulled it out just to see if being in the field list was the problem.
>>
>>Now, how do I restructure the above to not need the G. reference?
>

>select G.cStyle, G.kGarment, G.cDescrip, G.nDefPrice, IIF(ISNULL(LG.ID),'X','L') as cType ;
>  from Garments G
>LEFT JOIN LocGarment LG ON (LG.kGarment = G.kGarment) AND (LG.kLoc = lkLoc)
>