>
>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