>>

Create Procedure NoNeedForAnSP @myValue int
>> as
>>select TableP.*, tableX.FK, tableX.sales
>> from tableP inner join
>> (select * from tableA
>>  union
>>  select * from tableB
>>  union
>>  select * from tableC
>>  union
>>  select * from tableD) tableX
>>on tableP.PK = tableX.FK
>>where tableP.PK = @myValue

>>
>>Brilliant Cetin!! Thank you very much.
>>Now a followup question (before I give you stars). Your solution works, but at what cost? Does this solution select all the records from TableA, TableB, TableC and TableD and place them into a temporary table TableX?
>>
>>Thanks,
>>Einar
>
>In effect yes. I think your design was wrong from the start. You could partition the table from the start and MSSQL would know which one to put where. You'd be doing a single 2 table join then.
>
>You might also make it as:
>
>

>select * from tableP inner join tableA on tableP.PK = tableA.FK where tableP.PK = @myValue
>union
>select * from tableP inner join tableB on tableP.PK = tableB.FK where tableP.PK = @myValue
>union
>select * from tableP inner join tableC on tableP.PK = tableC.FK where tableP.PK = @myValue
>union
>select * from tableP inner join tableD on tableP.PK = tableD.FK where tableP.PK = @myValue
>

>Which probably would be more effective. Or:
>

select TableP.*, tableX.FK, tableX.sales
> from tableP inner join
> (select * from tableA where tableA.FK = @myValue
>  union
>  select * from tableB where tableB.FK = @myValue
>  union
>  select * from tableC where tableC.FK = @myValue
>  union
>  select * from tableD where tableD.FK = @myValue
>) tableX
>on tableP.PK = tableX.FK

Cetin
>
>Cetin


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