>>
>>Cetin,
>>Thanks for the reply and the additional solutions. I will try to review the solutions in greater detail tomorrow. Yes the table design might be a little goofy, but tere are several fileds in the different tables (TableA, TableB, TableC and TableD) that are different.
>>
>>Also there is one piece of information that has not been used yet. Based on the field TableP.Type I know which table the child records can be found, so if TableP.Type='A' then I know that the child records will always be in TableA. I guess this could be solved with some sort of IF or SWITCH structure if I do a select on TableP for the PK first. Several ways to skin the cat I guess.
>>
>>Again thanks for your reply and hopefully the information above will help you give me another brilliant solution.
>>
>>Thanks,
>>Einar
>
>Einar,
>Are you telling me that you only have 4 types defined with "A","B","C","D" typecode in main table and:
>if it's A result table is always named "sameTablePrefixA"
>if it's B result table is always named "sameTablePrefixB"
>...
>? In other words Subtype Generalization Hierarchy?
>If so again multiple solutions:) ie:
>
>

>create procedure MyProc @myPK int as
>begin
>declare @targetTable char(1)
>select @targetTable = [Type] from tableP where PK = @myPK
>
>if @targetTable = 'A'
>  select tp.*, ot.PK,ot.FK,ot.sales
>      from tableP tp left join tableA ot on  tp.PK = ot.FK
>      where tp.PK = @myPK
>if @targetTable = 'B'
>    select tp.*, ot.PK,ot.FK,ot.sales
>      from tableP tp left join tableB ot on  tp.PK = ot.FK
>      where tp.PK = @myPK
>if @targetTable = 'C'
>    select tp.*, ot.PK,ot.FK,ot.sales
>      from tableP tp left join tableC ot on  tp.PK = ot.FK
>      where tp.PK = @myPK
>if @targetTable = 'D'
>    select tp.*, ot.PK,ot.FK,ot.sales
>      from tableP tp left join tableD ot on  tp.PK = ot.FK
>      where tp.PK = @myPK
>end

Cetin

Thanks Cetin. I ended up using the solution you posted.

Einar