CODEWITHSUNDEEP
mysqlsqljoinleft-join
Newbees
Newbees

Reputation: 89

Left join MIS-MATCH from ACTUAL values

After multiple time trying I'm unable to fix this left join query. My query returns different debit_amount from the actual whereas my actual debit_amount is lesser than the given for both of months.

My question is why my left join values mismatch from actual and how can I fix this to get actual values.

My complete query and results are shown below.

SELECT 
    MONTHNAME(cc.credit_date) as month,
    IFNULL(SUM(cc.credit_amount),0) AS credit,
    IFNULL(SUM(cd.debit_amount),0) AS debit_amount,
    MONTHNAME(cd.debit_date) AS month
FROM 
    cust_credit AS cc
LEFT JOIN
    (SELECT 
         ((cd.debit_date)), 
         IFNULL(SUM(cd.debit_amount), 0) AS debit_amount 
     FROM 
         cust_debit AS cd 
     GROUP BY 
         MONTHNAME(cd.debit_date)) cd ON ((MONTHNAME(cd.debit_date)) = MONTHNAME(cc.credit_date))
GROUP BY 
    MONTHNAME(cc.credit_date)

Result of that query in a screenshot:

JOIN QUERRY

While if I run only join to chec. the query is

  SELECT (MONTHNAME(cd.debit_date)) , IFNULL(SUM(cd.debit_amount),0) AS debit_amount FROM cust_debit AS cd GROUP BY MONTHNAME(cd.debit_date )

Results of that query:

[RESULT IMAGE[2]

CREDIT TABLE

cust_credit

DEBIT TABLE

cust_debit

Upvotes: 1

Views: 66

Answers (1)

Ravi
Ravi

Reputation: 31417

As per theory, LEFT JOIN will give you data, which is common between table and additionally from left table.

So, it has more chance that, you will get more rows in case of LEFT JOIN compare to INNER JOIN or directly selecting row from one of the table.

Now, you will look the the query, you have made join on below condition.

ON ( (MONTHNAME(cd.debit_date)) = MONTHNAME(cc.credit_date))

So, you will get all rows from cust_credit table and additionally where above condition satisfies.

==Update==

This is sample SQL, you might need to make changes accordingly. So, you are trying to get all credit and debit for specific month. This can be achieved in simple INNER JOIN between the tables.

SELECT cd.month, cc.credit_amount, cd.debit_amount FROM 
(SELECT 
MONTHNAME(cc.credit_date) as month,
IFNULL(SUM(cc.credit_amount),0) AS credit_amount
FROM
cust_credit cc 
GROUP BY MONTHNAME(cc.credit_date)) cc

INNER JOIN 

(SELECT 
MONTHNAME(cd.debit_date) as month,
IFNULL(SUM(cd.debit_amount),0) AS debit_amount
FROM
cust_debit cd
GROUP BY MONTHNAME(cd.debit_date)) cd ON cc.month=cd.month;

Upvotes: 2

Related Questions