Joining more than 2 tables based on value in mysql

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)
+-----+-----+-----+-----+----+--------+------+-----+

1 answer

  • answered 2017-11-12 20:49 spencer7593

    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.