CODEWITHSUNDEEP
sqloracle-database
Aldrin Bunche
Aldrin Bunche

Reputation: 25

Query not show any records if one column on table has no data

The below query returns 0 rows if a value is not present on paymatic_debtors_info table:

SELECT DISTINCT 
       prj$quote_sections.quote_section_id,
       cl$invoices.invoice_id,
       cl$invoices.invoice_no,
       cl$invoices.total Invoiced,
       paymatic_debtors_info.amount Paid
FROM   cl$invoices
       INNER JOIN cl$Invoice_items
       ON cl$Invoice_items.invoice_id = cl$invoices.invoice_id
       INNER JOIN paymatic_debtors_info
       ON paymatic_debtors_info.REF = cl$invoices.invoice_no
       inner join VW_PM_INV_BAL
       on VW_PM_INV_BAL.INVOICE_NO = paymatic_debtors_info.REF
       INNER JOIN prj$quote_items
       ON rj$quote_items.quote_item_id=cl$Invoice_items.quote_item_id
       INNER JOIN prj$quote_sections
       ON prj$quote_sections.quote_id    = prj$quote_items.quote_id
       AND prj$quote_sections.quote_section_id = '1000015948'
       and paymatic_debtors_info.TYPE = 'Pmt'
       or paymatic_debtors_info.TYPE = 'Crd'

I have tried union queries, if statements - same result.

Expected Results:

Quote_Section_id Invoice_id Invoice_no Invoiced Paid
1000065052       7290012356 325698     3530

Upvotes: 0

Views: 73

Answers (1)

MT0
MT0

Reputation: 168051

Without some sample data to replicate the problem it is a bit difficult to answer but this might give a solution:

SELECT DISTINCT 
       qs.quote_section_id,
       i.invoice_id,
       i.invoice_no,
       i.total Invoiced,
       pdi.amount Paid
FROM   cl$invoices i
       INNER JOIN cl$Invoice_items ii
       ON ( ii.invoice_id = i.invoice_id )
       INNER JOIN prj$quote_items qi
       ON ( qi.quote_item_id = ii.quote_item_id )
       INNER JOIN prj$quote_sections qs
       ON ( qs.quote_id    = qi.quote_id )
       LEFT OUTER JOIN (
         SELECT pdi.REF,
                pdi.Amount
         FROM   paymatic_debtors_info pdi
                INNER JOIN VW_PM_INV_BAL ib
                ON ( ib.INVOICE_NO = pdi.REF )
         WHERE  pdi.TYPE IN ( 'Pmt', 'Crd' )
       ) pdi
       ON ( pdi.REF = i.invoice_no )
WHERE  qs.quote_section_id = '1000015948'

Upvotes: 2

Related Questions