>>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.
>
>
>here is one of my attempts that is not working...
>
>
>Select model.name as modelname, option.name as optionname, optionpackage.name as packagename;
> FROM modeloption ;
> JOIN option ;
> ON option.optionid = modeloption.optionid ;
> JOIN Option OptionPackage ;
> ON Option.optionid = modeloption.packageid ;
> JOIN Model ;
> ON model.modelid = modeloption.modelid ;
> INTO CURSOR packagelist
>
>
>
>-----------------------
>
>My concern also is that inner joins may not return ALL the contents of the packages and instead will only return one record for each package.


Oh one more thing... to make things a bit more complicated I only need the packages and the options in the packages. I want to filter out any options that are not packages or options in a package.