>

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