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.