0

Possible duplicate with :

Setting a foreign key to more than one table

OR

has_one with more than one possible foreign key column

I have a table(MainTable) in my sql database which has 5 field.One of these fields(FID) is foreign key of 5 other tables in the same database.The other field(TypeID) is containing an ID which shows that the key in FID is belong to which table of that 5 tables.For example if FID=1000 and TypeID=1 for a record in MainTable it means that 1000 is a primary key in table number one.

Now i want to join these tables to a new table so i have made a view and used LEFT OUTER JOIN command between each of those 5 tables and MainTable :

    Select * From 
      (Select * From 
        (Select * From 
           (Select * From 
             (Select * From MainTable LEFT OUTER JOIN Table1 ON MainTable.FID=Table1.ID AND  MainTable.TypeID=1)AS Temp1 
LEFT OUTER JOIN Table2 ON Temp1.FID=Table2.ID AND Temp1.TypeID=2)AS Temp2 
  LEFT OUTER JOIN Table3 ON Temp2.FID=Table3.ID AND Temp2.TypeID=3)AS Temp3 
    LEFT OUTER JOIN Table4 ON Temp3.FID=Table4.ID AND Temp3.TypeID=4)AS Temp4 
       LEFT OUTER JOIN Table5 ON Temp4.FID=Table5.ID AND Temp4.TypeID=5

1)Is the above query the best one for this aim?

2)Is there any way to merge some columns of the result table in a special column?I mean that if the result is something like this :

field1       field2      field3      field4
-------------------------------------------
value1        NULL        NULL        NULL
 NULL        value2       NULL        NULL
 NULL         NULL       value3       NULL
 NULL         NULL        NULL       value4

convert it to something like below :

  new_field
------------
   value1
   value2
   value3
   value4
Community
  • 1
  • 1
M_Mogharrabi
  • 1,369
  • 5
  • 29
  • 57

1 Answers1

0

My question answered in this url by CHill.

M_Mogharrabi
  • 1,369
  • 5
  • 29
  • 57