MySQL combine results of two unrelated tables-CodePudding

I'm trying to merge two query results in to one: Query 1 and the reulsts:

    SELECT MONTHNAME(tblFeesPaid.Pay_Date) AS 'Month', 
            SUM(tblFeesPaid.Fees_Paid) As 'Total Fees' 
    FROM tblFeesPaid 
        INNER JOIN tblFeesStructure ON tblFeesPaid.FID=tblFeesStructure.ID 
    WHERE Year(tblFeesPaid.Pay_Date)=2022 
    GROUP BY month(tblFeesPaid.Pay_Date);

Results

    Month    Total Fees 
    January  162000.00
    February 69000.00
    March    146926.00

Query 2 and results

    SELECT MONTHNAME(tblTransFeesPaid.Pay_Date) AS 'Month', 
            SUM(tblTransFeesPaid.TransFee_Paid) As 'Transport Fees' 
    FROM tblTransFeesPaid 
        INNER JOIN tbltransfeesstructure ON tblTransFeesPaid.TransFID=tbltransfeesstructure.ID 
    WHERE Year(tblTransFeesPaid.Pay_Date)=2022 
    GROUP BY month(tblTransFeesPaid.Pay_Date);

Results

    Month Transport Fees
    March 7000.00

Could someone help me with the correct syntax that I'll achieve results as follows:

Expected results:

 Month    Total Fees  Transport Fees
 January  162000.00
 February 69000.00
 March    146926.00   7000.00

CodePudding user response：

SELECT Z.Month, sum(Z.TotalFees) As 'Total Fees', sum(Z.TransportFees) As 'Transport Fees'
FROM
(
SELECT MONTHNAME(tblFeesPaid.Pay_Date) AS 'Month',
       SUM(tblFeesPaid.Fees_Paid) As 'TotalFees',
       0 As 'TransportFees'
FROM tblFeesPaid 
INNER JOIN tblFeesStructure ON tblFeesPaid.FID=tblFeesStructure.ID 
WHERE Year(tblFeesPaid.Pay_Date)=2022 
GROUP BY month(tblFeesPaid.Pay_Date)
UNION
SELECT MONTHNAME(tblTransFeesPaid.Pay_Date) AS 'Month',
       0 As 'TotalFees',
       SUM(tblTransFeesPaid.TransFee_Paid) As 'TransportFees' 
FROM tblTransFeesPaid 
        INNER JOIN tbltransfeesstructure ON tblTransFeesPaid.TransFID=tbltransfeesstructure.ID 
    WHERE Year(tblTransFeesPaid.Pay_Date)=2022 
    GROUP BY month(tblTransFeesPaid.Pay_Date)) Z
GROUP BY Z.Month;

CodePudding user response：

First, you make sure both queries return same number of columns (add 0 as Transport Fees to first Query, and add 0 as Total Fees in second query);

Then, you UNION the queries, having one big resultset.

After that it's group by Month, and get MAX(Total Fees) and MAX(Transport Fees).

Would something like that work?

i.e.

    SELECT Month, MAX(`Total Fees`), MAX(`Transport Fees`) FROM (

    (
        SELECT MONTHNAME(tblFeesPaid.Pay_Date) AS 'Month', 
                SUM(tblFeesPaid.Fees_Paid) As 'Total Fees',
                0 AS 'Transport Fees'
        FROM tblFeesPaid 
        INNER JOIN tblFeesStructure ON tblFeesPaid.FID=tblFeesStructure.ID 
        WHERE Year(tblFeesPaid.Pay_Date)=2022 
        GROUP BY month(tblFeesPaid.Pay_Date)
    ) t1

    UNION

    (
        SELECT MONTHNAME(tblTransFeesPaid.Pay_Date) AS 'Month', 
                0 AS 'Total Fees',
                SUM(tblTransFeesPaid.TransFee_Paid) As 'Transport Fees' 
        FROM tblTransFeesPaid 
        INNER JOIN tbltransfeesstructure ON tblTransFeesPaid.TransFID=tbltransfeesstructure.ID 
        WHERE Year(tblTransFeesPaid.Pay_Date)=2022 
        GROUP BY month(tblTransFeesPaid.Pay_Date)
    ) t2

  ) t3

  GROUP BY Month;