CODEWITHSUNDEEP
mysqlsql
Neetu
Neetu

Reputation: 117

Combine multiple tables to generate a view

I have 4 tables, 3 for incoming money, 1 for outgoing money

credittable1

uid | amnt | dat
1   | 50   |31-02-2016
2   | 40   |22-03-2017
...and so on

credittable2

uid | amnt | dat
1   | 60   |01-03-2016
2   | 20   |22-02-2017
...and so on

credittable3

uid | amnt | dat
1   | 70   |03-03-2016
2   | 10   |21-03-2017
...and so on

debittable

uid | amnt | dat
1   | 120  |12-04-2016
2   | 80   |25-02-2017
...and so on

I need to combine all 4 tables to look like an account book of credits and debits along with the balance amount at that transaction in datewise ordering for every user. Eg., I want to print the account book of uid 1, then all his transactions should be printed like an account book in the order of date the credit or debit transaction occurred along with the balance amount at that transaction (just like a bank passbook).

All tables are really heavy with a large number of records, so I am looking for a fast (good performance) solution.

Final implementation needs to be done in Codeigniter.

A user may get 10 (or any number) transactions in table 1, and so on in all tables. Number of records for each user in each table may vary.

Output would look like this:

uid | date | credit | debit | balance amount

Please suggest a way to achieve this. I cannot decide whether it should be done using Joins or some other strategy.

Upvotes: 1

Views: 113

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think I would do this to get the base data:

select uid, date, sum(credit) as credit, sum(debit) as debit
from ((select uid, dat amt as credit, 0 as debit
       from credittable1
      ) union all
      (select uid, dat amt as credit, 0 as debit
       from credittable2
      ) union all
      (select uid, dat amt as credit, 0 as debit
       from credittable3
      ) union all
      (select uid, dat, 0, - amt
       from debittable
      )
     ) ad
group by uid, dat
order by uid, date;

Then you can use variables to get the balance:

select uid, date,
       (@b := if(@u = uid, @u + credit + debit,
                 if(@u := uid, credit + debit, credit + debit)
                )
       ) as balance
from (select uid, date, sum(credit) as credit, sum(debit) as debit
      from ((select uid, dat amt as credit, 0 as debit
             from credittable1
            ) union all
            (select uid, dat amt as credit, 0 as debit
             from credittable2
            ) union all
            (select uid, dat amt as credit, 0 as debit
             from credittable3
            ) union all
            (select uid, dat, 0, - amt
             from debittable
            )
           ) ad
      group by uid, date
      order by uid, date
     ) ud cross join
     (select @u := -1, @b := 0) balance;

Or in MySQL 8+, you could use a cumulative sum:

sum(debit + credit) over (partition by uid order by date)

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

A UNION ALL over all tables gives you the basis (and you should consider to redesign the schema and just use only one such table from the start).

For the balance, you can use a subquery, that gets the sum of "previous" amounts. "Previous" here means bookings with a date less than or equal the date of the current booking. As there might be more bookings on one day, we need a second criteria to tell them apart. That's where sno comes into play -- it also has to be less than or equal to the current one. To make sure a sno is unique across the entire set, not just the table it comes from, we prepend a unique table identifier.

In MySQL 8.0 a windowed sum could be used for that, making it far easier. But I assume you're on a lesser version.

SELECT x.amnt,
       x.dat,
       (SELECT coalesce(sum(amnt), 0)
               FROM (SELECT concat('c1-', sno) sno
                            amnt,
                            dat
                            FROM credittable1
                            WHERE uid = 1
                     UNION ALL
                     SELECT concat('c3-', sno) sno,
                            amnt,
                            dat
                            FROM credittable2
                            WHERE uid = 1
                     UNION ALL
                     SELECT concat('c3-', sno) sno,
                            amnt,
                            dat
                            FROM credittable3
                            WHERE uid = 1
                     UNION ALL
                     SELECT concat('d1-', sno) sno,
                            -1 * amnt,
                            dat
                            FROM debittable
                            WHERE uid = 1) y
               WHERE y.dat <= x.dat
                     AND y.sno <= x.sno) bal
       FROM (SELECT concat('c1-', sno) sno,
                    amnt,
                    dat
                    FROM credittable1
                    WHERE uid = 1
             UNION ALL
             SELECT concat('c2-', sno) sno,
                    amnt,
                    dat
                    FROM credittable2
                    WHERE uid = 1
             UNION ALL
             SELECT concat('c3-', sno) sno,
                    amnt,
                    dat
                    FROM credittable3
                    WHERE uid = 1
             UNION ALL
             SELECT concat('d1-', sno) sno,
                    -1 * amnt,
                    dat
                    FROM debittable
                    WHERE uid = 1) x
       ORDER BY x.dat;

Upvotes: 0

Mureinik
Mureinik

Reputation: 311308

You could union all the four tables, and negate the amounts in debittable:

SELECT uid, amt,      dat FROM credittable1 UNION ALL
SELECT uid, amt,      dat FROM credittable2 UNION ALL
SELECT uid, amt,      dat FROM credittable3 UNION ALL
SELECT uid, -1 * amt, dat FROM debittable

Upvotes: 1

Related Questions