Thanks a bunch guys you have been a big help. Naturally, as it seems when I tried to put the final touches on things got more hairy.

In modeloption there is a billcode I need as well. Just found out that I absolutely have to have it in the result set.

So my end result needs to be like:

Modelid     Option.name                            Optionpackage.name                    Packagebillcode                                  Optionbillcode
-------------- ----------------------------------------        ---------------------------------------------      -----------------------                                   ----------------------
180           Option.name for optionid=10     Option.name for optionid=100         modeloption.billcode (package)     modeloption.billcode (option)
180           Option.name for optionid=20     Option.name for optionid=100         modeloption.billcode (package)     modeloption.billcode (option)
180           Option.name for optionid=30      Option.name for optionid=100        modeloption.billcode (package)     modeloption.billcode (option)

Can't believe I'm struggling with this so much... Just seems i'm missing something basic.

Thanks again for all your help!


>>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!!!
>>
>It still would be a self-join, e.g.
>

>select M1.ModelID, M.Name as ModelName, O1.Name as OptionName, O2.Name as PackageName from Models M1
> inner join ModelOptions MO on M1.ModelID = MO.ModelID 
>inner join Options O1 on MO.OptionID = O1.OptionID inner join Options O2 on MO.PackageID = O2.OptionID