SQL statement - dynamically merging columns from different tables into a single result set

I am not sure this is possible with SQL (say we are executing on DB2) - infact I would say it is not, but thought I would ask some folk who are more experienced in SQL than I am to comment. I have described the problem and what I would like to happen below. Please let me know if you see a way to do this, if not I guess I am down the path of retrieving the data in batches or something like that. Thanks a lot.

MAIN TABLE

| REF_TABLE   | RECORD_NO      | GROUPID  |
|   TABLE1    |   1            |  BLUE    |
|   TABLE1    |   2            |  BLUE    |
|   TABLE2    |   3            |  GREEN   |
|   TABLE3    |   4            |  BLUE    |
|   TABLE2    |   5            |  GREEN   |
|   TABLE4    |   6            |  BLUE    |

TABLE1

|  RECORD_NO |   NAME    | VALUE     |
|   1        |   NAMEX   |  RANDOM1  |
|   2        |   NAMEY   |  RANDOM2  |

TABLE2

|  RECORD_NO |   NAME    |  VALUE     |
|   3        |   NAMEB   |  RANDOM10  |
|   5        |   NAMEC   |  RANDOM9   |

TABLE3

| RECORD_NO  | NAME      | VALUE      |
|    4       |   NAMET   |  RANDOM77  |

TABLE4

| RECORD_NO  | NAME      | VALUE      |
|   6        |   NAMET   |  RANDOM77  |
|   7        |   NAMEZ   |  RANDOM99  |

So I have some criteria for querying MAIN TABLE e.g.

SELECT REF_TABLE, RECORD_NUMBER WHERE GROUPID = 'BLUE'

But I also want to include the appropiate values from the other tables which are referenced via REF_TABLE. So the result would be:

| REF_TABLE   | RECORD_NUMBER  | NAME    | VALUE     |
|   TABLE1    |   1            | NAMEX   |  RANDOM1  |
|   TABLE1    |   2            | NAMEY   |  RANDOM2  |
|   TABLE3    |   4            | NAMET   |  RANDOM77 |
|   TABLE4    |   6            | NAMET   |  RANDOM77 |

In this case TABLE1, TABLE3 and TABLE4 have their name and value columns merged 开发者_开发技巧into a single result set. The table is of course retrieved from the ref_table column of main table. The list of ref tables in finite, so I could hardcode the table names in SQL statements in the query (to avoid dynamically setting the table name) e.g. IF TABLE1 SELECT FROM SCHEMA.TABLE1 (maybe CASE).

Restructuring the tables is not an option and the number of returned results may be 10000s.

Preferable I would want this all as a single query.

Thanks.

One can also take the UNIONs from Hemal Panya's answer out of the view and into your query...

SELECT record_no, groupid, name, value
FROM main_table INNER JOIN ref_table1 on main_table.record_no = ref_table1.record_no
WHERE main_table.ref_table = 'TABLE1'

UNION ALL

SELECT record_no, groupid, name, value
FROM main_table INNER JOIN ref_table2 on main_table.record_no = ref_table2.record_no
WHERE main_table.ref_table = 'TABLE2'

UNION ALL

etc, etc...

This is probably much faster than tryign to use CASE...

SELECT
  main_table.record_no,
  main_table.groupid,
  CASE main_table.ref_table WHEN 'Table1' THEN Table1.name
                            WHEN 'Table2' THEN Table2.name
                            etc, etc
  END,
  CASE main_table.ref_table WHEN 'Table1' THEN Table1.value
                            WHEN 'Table2' THEN Table2.value
                            etc, etc
  END
FROM
  main_table
LEFT JOIN
  Table1
    ON  main_table.record_no = Table1.record_no
    AND main_table.ref_table = 'Table1'
LEFT JOIN
  Table2
    ON  main_table.record_no = Table2.record_no
    AND main_table.ref_table = 'Table2'
etc, etc

I would, however, recommend against any of these options. It feels as though your schema is designed against the natural behaviour of SQL. You may either need a new structure, or be better suited to a different environment. Without knowing more details, however, it's impossible to advise on a natural relational structure that would meet your needs without needing this kind of 'conditional join'.

On that basis, I'd be intrigued to know why it is that you are hesitent to use a view to unify your disperate data. It appears, in a vacuum, to be th emost sensible option...

using case is certainly a possibility and should work.

Another option is to

create view REF_TABLE as
  select 'TABLE1' as TABLE_NAME, RECORD_NO, NAME, VALUE from TABLE1 
  UNION select 'TABLE2' as TABLE_NAME, RECORD_NO, NAME, VALUE from TABLE2 
  ...

And then,

select RECORD_NO, GROUPID, NAME, VALUE 
from MAIN_TABLE 
  join REF_TABLE on MAIN_TABLE.REF_TABLE = REF_TABLE.TABLE_NAME and
                    MAIN_TABLE.RECORD_NO = REF_TABLE.RECORD_NO

etc

EDIT:

Not sure why you want to avoid the view. Given your schema it might be useful elsewhere too, and give you significant performance benefit if you can materialize your views (look it up if you don't know what they are)

The query using case would be something as follows. I am not very clear on sql server case syntax so you will have work on that.

select m.RECORD_NO, m.GROUP_ID,
case when REF_TABLE = 'TABLE1' then t1.NAME, t1.VALUE
else when REF_TABLE = 'TABLE2' then t2.NAME, t2.VALUE
...
end case,
from MAIN_TABLE M
  left outer join TABLE1 T1 on M.RECORD_NO = T1.RECORD_NO
  left outer join TABLE2 T2 on M.RECORD_NO = T2.RECORD_NO
....