Try
SELECT mo.Modelid, pnm.Name AS PackageName, onm.Name AS OptionName
FROM modeloption mo
JOIN Option AS pnm ON pnm.optionid = mo.Packageid
JOIN Option AS onm ON onm.optionid = mo.Optionid
>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
>
>
>Thanks a bunch for your help!!!
>
>>What table PackageID in Modeloption table links to? How is it related to the OptionID in the same row in the table?
>>
>>>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.
--sb--