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

I think I see where you were going with the code you provided. I'll keep working in that direction to see if I can get to where I need.

I've included my clarifications for Sergey here below in a better format.


Packageid refers to the option.optionid of the parent. I realize now I confused things. It's not a self join.

option.optionid = modeloption.packageid for the contents of a package

So a package with 3 records would look like this basically...

     Modelid     Optionid     Packageid

         180              10              100
         180              20              100
         180              30              100

option.optionid = modeloption.optionid for the parent of the package

         180            100                  0   (0 or null would indicate a parent record)


The result set I want would be:

      Modelid     Option.name                                       Optionpackage.name
      --------------   ----------------------------------------                 ---------------------------------------------
         180        Option.name for optionid=10                 Option.name for optionid=100
         180        Option.name for optionid=20                 Option.name for optionid=100
         180        Option.name for optionid=30                 Option.name for optionid=100