>Ok this should be easy but is a bit of a tough for me this morning...
>
>I have three main tables with the important fields listed in ():
>
>Model - list of models (modelid, name)
>Option - list of options (optionid, name, ispackage)
>modeloption - list of options on a given model (optionid, modelid, packageid, billcode)
>
>In modeloption the packageid self-joins to denote the contents of the package. So if a given modeloption record had an optionid of 102, then I could query modeloption where packageid=102 get the contents of the package.
>
>The result I want is this...
>
>Model, Package Name (option.name for top level), Option Name (option.name for the children), Billcode
>
>So the package name would be duplicated for each record in the contents. It is ok if there is a record in which the package name and option name are both listed.
>
>What is making it tough for me a bit is the join against the modeloption is on modelid AND optionid. That I think is tripping up my join syntax.
>
>Any help is appreciated.

What seems to be the problem?

Select M1.Name as ModelName, O.Name as OptionName, IIF(O.IsPackage, O.Name, space(30)) as PackageName
  from Models M1 inner join ModelOptions MO on M1.ModelID = MO.ModelID inner join Option O on MO.OptionID = O.OptionID where O.IsPackage = .F.
union all
Select M1.Name as ModelName, O.Name as OptionName, IIF(O.IsPackage, O.Name, space(30)) as PackageName
  from Models M1 inner join ModelOptions MO on M1.ModelID = MO.ModelID inner join Option O on MO.PackageID = O.OptionID
where O.IsPackage = .T.