PHP-MySql帮助从两个表中选择记录制作日志(会计)

Question

我有两个表:

1. 外向-每天发生的所有交易的数据

2. 收据-收到的款项信息

   向外

   :

   bill_No      date            perticular      amount      cust_Id     cust_Name
   1           2013-06-21       Any Item 1      1250           1        Vikram
   2           2013-06-21       Any Item 2      1500           1        Vikram 
   ------- & so on------
   

   收据:

   rec_Id     date              payment_mode     amount     cust_Id     cust_Name
   1         2013-06-21         Cash             1000        1          Vikram
   2         2013-06-22         Cash             1750        1          Vikram
   ------- & so on------
   

预期输出(屏幕显示):

Date            Particulars       Bill No     Debit        Credit        Balance
2013-06-21      Any Item 1           1         1250          -
2013-06-21      Any Item 2           1         1250          -
2013-06-21      Cash                 -           -          1000
2013-06-22      Cash                 -           -          1750

我想做的是:[for making of Journal]
从两个表中收集特定客户(cust_Name)的所有记录，并根据日期对它们进行排序
[在相同的日期:首先从外向表中获取记录，然后从收据中获取记录]。

我应该执行什么查询?

注。-我会自己计算余额

Answer 1

你在找这个吗?

SELECT date, perticular, bill_No, debit, credit, balance
  FROM
(
  SELECT date, perticular, bill_No, amount debit, NULL credit, NULL balance, 1 ord
    FROM outward
   WHERE cust_id = 1
   UNION ALL
  SELECT date, payment_mode, NULL, NULL, amount, NULL, 2
    FROM receipt
   WHERE cust_id = 1
) q
ORDER BY date, ord

输出:

<>之前日期/账单/借方/贷方/余额-----------------------------------------------------------------2013-06-21 | Any Item 1 | 1 | 1250 | (null) | (null) |2013-06-21 |任意项2 | 2 | 1500 | (null) | (null) |2013-06-21 | Cash | (null) | (null) | 1000 | (null) |2013-06-22 | Cash | (null) | (null) | 1750 | (null) |之前

这里是SQLFiddle demo