MySQL Join - 引用三个以上的表并获取与主表匹配的值

Question

I'm having a table DailyTransaction, it contains day to day shop transactions like sales, purchase, bank Receipts and Vouchers and Expense Vouchers, etc.,

Main Table: DailyTransaction

SNo         Mode            title
---------------------------------------
1             1            Sales
2             1            Sales
3             3            Bank Payment
4             2            Purchase
5             1            Sales
6             2            Purchase
7             2            Purchase
8             1            Sales
9             3            Bank Payment

Table: Sales

SNo           DSNo          Amount
---------------------------------------
101             1            200
102             2            500
103             5            800
104             8            250

Table: Purchase

SNo           DSNo          Amount
---------------------------------------
201             7            850
202             6            525
203             7            650

Table: Bank

SNo           DSNo          Amount
------------------------------------
301             3            450
302             9            875

My Requirement is

SNo         Mode            title           DSNo          Amount
-----------------------------------------------------------------
1             1            Sales1            101            200
2             1            Sales             102            500
3             3            Bank Payment      301            450
4             2            Purchase          201            850
5             1            Sales             103            800
6             2            Purchase          202            525
7             2            Purchase          203            650
8             1            Sales             104            250
9             3            Bank Payment      302            875

The Column DSNo is a Primary SNo Column of main table DailyTransaction Column Mode specifies the type of transaction.

Kindly assist me how to get the expected output?

Answer 1

Your table structure makes this harder than it might otherwise need to be (why 3 separate tables for Sales, Purchase, and Bank, as opposed to a single table with a flag for what type it is, for instance?), but this query should give you what you're looking for:

SELECT
    dt.SNo
    , dt.Mode
    , dt.title
    , COALESCE(Sales.SNo, Purchase.SNo, Bank.SNo) AS DSNo
    , COALESCE(Sales.Amount, Purchase.Amount, Bank.Amount) AS Amount
FROM DailyTransaction AS dt
LEFT JOIN Sales ON dt.SNo = Sales.DSNo
LEFT JOIN Purchase ON dt.SNo = Purchase.DSNo
LEFT JOIN Bank ON dt.SNo = Bank.DSNo

The issue of course being that you want to display the "Amount" column from different tables depending on what kind of transaction it is, which makes it slightly more involved than a simple join. The article Sean linked to in the comment is definitely a good read though.