Query multiple tables on the same table

0

Hello good day my problem is this: I have a structure similar to this one:

table masterTable
+------------------------+
| Field                  |
+------------------------+
| id                     |
| idDoc                  |
| doc                    |
+------------------------+

table doc1
+------------------------+
| Field                  |
+------------------------+
| id                     |
| info                   |
+------------------------+

table doc2
+------------------------+
| Field                  |
+------------------------+
| id                     |
| info                   |
+------------------------+

With it I intend that masterTable can extract information from tables doc1, doc2 and other doc more communicating through the doc field that serves to identify which table to consult. Assuming that masterTable has this information table masterTable

+-------------------
| id | idDoc | doc |
+----+--------------
|  1 |     1 |   1 |
|  2 |     1 |   2 |
|  3 |     2 |   2 |
|  4 |     3 |   2 |
|  5 |     2 |   1 |
+-------------------

With this qry I extract the info field from the doc tables:

SELECT 
    masterTable.id,
    docs.info
FROM masterTable
INNER JOIN (
    SELECT 
        '1' as doc,
        id,
        info
    FROM doc1
    UNION ALL
    SELECT 
        '2' as doc,
        id,
        info
    FROM doc1 
) docs ON docs.id = masterTable.idDoc 
    AND docs.doc = masterTable.doc

The behavior is correct and I get the desired information, my problem is that I feel that at some point the masterTable table can grow so much and consult too many doc tables (at least 10) and the query becomes heavy, there is some way to make the structure or the qry more efficiently or this Is it the right one? Thank you very much in advance.

    
asked by Luis Lopez Moreno 05.07.2018 в 22:31
source

0 answers