How can I check whether my inner join is right or wrong?

Question

select a. App_No, b.App_no, a.Date, Sum(b.RECEIVABLE_AMNT) Prin_Dis_Amount , c.App_no, 
    sum(c.RECEIVABLE_AMNT) Ins_Dis_Amount , d.App_no, sum(d.RECEIVABLE_AMNT) OPE_Dis_Amount 
FROM odk_prod.receivables_test a, odk_prod.receivables_test b, odk_prod.receivables_test c, odk_prod.receivables_test d 
WHERE a.app_no=b.app_no or Null 
    and a.app_no=c.app_no or Null 
    and a.app_no=d.app_no or Null 
    and b.particulars like 'Principal%' 
    and (b.Vouch_name like 'Journal Voucher%' 
        Or b.Vouch_name like 'Disbursement%' 
        Or b.Vouch_name like 'Internal Adjustment%' 
        Or b.Vouch_name like 'Adjustment - Resale%') 
    and c.particulars like 'Disbursement%' 
    and c.vouch_name like 'Insurance - JV%' 
    and d.particulars like 'Disbursement%' 
    and (d.vouch_name like 'Post Sanction OPE%' 
        or d.vouch_name like 'Reimbursement%' 
        or d.vouch_name like 'Fee Debit OPE%' 
        or d.vouch_name like 'Franking%') 
    and a.APP_NO = 'APP-002-066' 
Group By a.App_no

`a.app_no = b.app_no or Null` is not correct. What are you trying to do with that syntax? — Barmar, Jul 16 '19 at 07:45
See https://stackoverflow.com/questions/37978511/join-tables-with-sum-issue-in-mysql/37979049#37979049 for how to get correct sums when you're joining multiple tables. — Barmar, Jul 16 '19 at 07:46
Why do you need to select `a.app_no`, `b.app_no`, `c.app_no`, and `d.app_no`? The joining condition means they're all the same. — Barmar, Jul 16 '19 at 07:48
In an inner join, `b.app_no`, `c.app_no`, `d.app_no` will never be null. That can only happen with `LEFT JOIN`. — Barmar, Jul 16 '19 at 07:50
Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS `X or null` is X. Likely you want something like `a.app_no=b.app_no or b.app_no is null`. But `or` binds more tightly than `and` so likely you want something like `(a.app_no=b.app_no or a.app_no is null)`. Start with tiny working code then repeatedly add & test. — philipxy, Jul 16 '19 at 21:36

score 0 · Answer 1 · answered Jul 16 '19 at 07:58

0

  WHERE a.app_no=b.app_no or Null 
     and a.app_no=c.app_no or Null 
     and a.app_no=d.app_no or Null

There is something wrong throwing NULL out of nowhere. If you are trying to find NULL it has to be this way:

 app.app IS NULL

answered Jul 16 '19 at 07:58

dodzb

379
2
5

score 0 · Answer 2 · answered Jul 16 '19 at 08:04

Get rid of or Null in your joining conditions. If you want the result to include rows in a that don't have matches in the other tables, use LEFT JOIN.

You don't need multiple joins with odk_prod.receivables_test. See need to return two sets of data with two different where clauses for how to sum different row types without self-joins.

SELECT app_no, date,
    SUM(IF(Vouch_name like 'Journal Voucher%' 
            Or Vouch_name like 'Disbursement%' 
            Or Vouch_name like 'Internal Adjustment%' 
            Or Vouch_name like 'Adjustment - Resale%'), receivable_amnt, 0) AS Prin_Dis_Amount,
    SUM(IF(particulars like 'Disbursement%' 
            and vouch_name like 'Insurance - JV%'), receivable_amnt, 0) AS Ins_Dis_Amount,
    SUM(IF(particulars like 'Disbursement%' 
            AND (vouch_name like 'Post Sanction OPE%' 
                or vouch_name like 'Reimbursement%' 
                or vouch_name like 'Fee Debit OPE%' 
                or vouch_name like 'Franking%'), receivable_amnt, 0) AS OPE_Dis_Amount
FROM odk_prod.receivables_test
GROUP BY app_no

How can I check whether my inner join is right or wrong?

2 Answers2