根据mysql中的值加入2个以上的表

Question

There is a project that I've to work on, the main transaction table and some sub transaction tables called provider. Each provider has its own table. The main table just keeps amount (as sub ones keep too), date and some essential data, also reference id of subtable. I want to join sub tables by based on provider id. If things can go messy, I can keep table names as an associative array. What makes me confused is each provider's table has a different primary key name.

Provider tables are pretty much identical excepts some columns. What I really try to achieve is performing a search in all of these 3 tables as one.

One other question, is this some silly idea, if so which approach would be better? Daily 400-500 records are expected. Also note, more provider tables can be added in future. This structure is designed by someone more experienced than me, I couldn't convince anyone this is bad.

Transaction
+-----+-----+-----+-----+
| id  | ref | prv | date|
+-----+-----+-----+-----+
| 1   | 4   | 2   | ..  |
+-----+-----+-----+-----+
| 2   | 4   | 3   | ..  |
+-----+-----+-----+-----+
| 3   | 5   | 2   | ..  |
+-----+-----+-----+-----+
| 4   | 7   | 1   | ..  |
+-----+-----+-----+-----+
| 5   | 22  | 3   | ..  |
+-----+-----+-----+-----+

Providers (prv value)
+-----+---------------+-----+
| pID | providerName  | ..  |
+-----+---------------+-----+
| 1   | providerA     | ..  |
+-----+---------------+-----+
| 2   | providerB     | ..  |
+-----+---------------+-----+
| 3   | providerC     | ..  |
+-----+---------------+-----+

p_providerA (ref value)
+-----+--------+------+-----+
| aID | amount | name | ..  |
+-----+--------+------+-----+
| 1   | 90.20  | alf  | ..  |
+-----+--------+------+-----+
| 2   | 70.00  |willie| ..  |
+-----+--------+------+-----+
| 3   | 43.10  | kate | ..  |
+-----+--------+------+-----+


p_providerB (ref value)
+-----+--------+------+-----+
| bID | amount | name | ..  |
+-----+--------+------+-----+
| 3   | 65.20  | jane | ..  |
+-----+--------+------+-----+
| 4   | 72.00  | al   | ..  |
+-----+--------+------+-----+
| 5   | 84.10  | bundy| ..  |
+-----+--------+------+-----+


p_providerC (ref value)
+-----+--------+------+-----+
| bID | amount | name | ..  |
+-----+--------+------+-----+
| 3   | 10.20  | mike | ..  |
+-----+--------+------+-----+
| 4   | 40.00  | kitt | ..  |
+-----+--------+------+-----+
| 6   | 27.60  | devon| ..  |
+-----+--------+------+-----+

Expected Result

+-----+-----+-----+-----+----+--------+------+-----+
| id  | ref | prv | date|    |        |      |     |
+-----+-----+-----+-----+----+--------+------+-----+
| 1   | 4   | 2   | ..  | 4  | 72.00  | al   | ..  | (from prv. b)
+-----+-----+-----+-----+----+--------+------+-----+
| 2   | 4   | 3   | ..  | 4  | 40.00  | kitt | ..  | (from prv. c)
+-----+-----+-----+-----+----+--------+------+-----+

Answer 1

Given the current table design, one of the ways to get the desired result is to "break down" the Transaction table into separate queries, and combine those with a UNION ALL

The rows from Transaction table could be returned like this:

SELECT t.* FROM Transaction t WHERE t.prv = 1
 UNION ALL
SELECT t.* FROM Transaction t WHERE t.prv = 2 
 UNION ALL
SELECT t.* FROM Transaction t WHERE t.prv = 3
 UNION ALL 
...

Now each of those SELECT can implement a join to the appropriate provider table

SELECT t.*, pa.amount, pa.name
  FROM Transaction t 
  JOIN p_providerA pa ON pa.aid = t.ref
 WHERE t.prv = 1

 UNION ALL

SELECT t.*, pb.amount, pb.name
  FROM Transaction t
  JOIN p_providerB pb ON pb.bid = t.ref
 WHERE t.prv = 2 

 UNION ALL 

   ...

---

The other option is almost equally ugly

SELECT t.*
     , CASE t.prv 
       WHEN 1 THEN pa.amount
       WHEN 2 THEN pb.amount
       WHEN 3 THEN pc.amount
       END AS `p_amount`
     , CASE t.prv 
       WHEN 1 THEN pa.name
       WHEN 2 THEN pb.name
       WHEN 3 THEN pc.name
       END AS `p_name`
  FROM Transaction t 
  LEFT JOIN p_providerA pa ON pa.aid = t.ref AND t.prv = 1
  LEFT JOIN p_providerB pb ON pb.bid = t.ref AND t.prv = 2
  LEFT JOIN p_providerC pc ON pc.cid = t.ref AND t.prv = 3

---

Bottom line... there's no way to dynamically use of the Providers table in a single query. We could make use of that information in a pre-query, to get back a resultset that helps us create the statement we need to run.

---

Another option (if the p_providerX tables aren't too large) would be to concatenate all of those together in an inline view, and the join to that. (This could be expensive for large sets; the derived table might get an index created on it...)

 SELECT t.*
      , p.amount  AS p_amount
      , p.name    AS p_name  
   FROM `Transaction` t
   JOIN (
          SELECT 1 AS pID, pa.aid AS rID, pa.amount, pa.name FROM p_providerA
           UNION ALL
          SELECT 2       , pb.bid      , pb.amount, pb.name FROM p_providerB
           UNION ALL
          SELECT 3       , pc.cid      , pc.amount, pc.name FROM p_providerC
           UNION ALL
             ...
        ) p
    ON p.pID  = t.pID
   AND p.rID  = t.ref

If we are going to be repeatedly running queries like that, we could materialize that inline view into a table... I'm just guessing at the datatypes here...

 CREATE TABLE p_provider
 ( pID      BIGINT UNSIGNED NOT NULL 
 , rID      BIGINT UNSIGNED NOT NULL
 , amount   DECIMAL(20,2)
 , name     VARCHAR(255)
 , PRIMARY KEY (pID,id) 
 );

 INSERT INTO p_provider (pID, rID, amount, name)
 SELECT 1 AS pID, pa.aid AS rID, pa.amount, pa.name FROM p_providerA
 ;
 INSERT INTO p_provider (pID, rID, amount, name)
 SELECT 2 AS pID, pb.aid AS rID, pb.amount, pb.name FROM p_providerB
 ;
 INSERT INTO p_provider (pID, rID, amount, name)
 SELECT 3 AS pID, pc.aid AS rID, pc.amount, pc.name FROM p_providerC
 ;
 ... 

And then reference the new table

SELECT ...
 FROM `Transaction` t 
 JOIN `p_provider` p 
   ON p.piD = t.pID
  AND p.rID = t.ref

Of course that new p_provider table is going to be out-of-sync when changes are made to p_providerA, p_providerB, et al.