duddosai duddosai - 7 months ago 13
SQL Question

Retrieving data from two different tables with common column using left outer join and on clause

Guys I have two different tables.

Table-1:

contract-id | contract-date
1 | 01-05-2016
1 | 01-06-2016
1 | 01-07-2016
2 | 01-01-2016
2 | 01-02-2016
3 | 01-05-2016
3 | 01-06-2016


Table-2:

invoice-number|contract-id|invoice-from-date|invoice-to-date
1 |1 |01-05-2016 |31-05-2016
2 |1 |15-05-2016 |31-05-2016
3 |1 |25-05-2016 |31-05-2016


My requirement is, I have to show what are all the contracts that comes in the current month from table-1 and I have to show the count of the invoice raised for those contracts from table-2. If there are any invoice raised, it should show its count or simply zero. The expected answer is:

contract-id|counts
1 |3
3 |0


The count for contract-id: 1 is 3, because I've raised three invoices for the current month and the count for contract-id: 3 is 0, because I haven't raised any invoice for it. And contract-id:2 is missing , because, it does not come under current month.

This is what I've tried:

select cbm_contract_period_value_split.contract_id,
count(cbm_contract_invoice_request.contract_id),
cbm_contract_period_value_split.contract_date
from cbm_contract_period_value_split
left outer join cbm_contract_invoice_request
on cbm_contract_period_value_split.contract_id = cbm_contract_invoice_request.contract_id
and MONTH(cbm_contract_period_value_split.contract_date) = MONTH(CURDATE())
and MONTH(cbm_contract_invoice_request.invoice_for_the_period_of_from_date) = MONTH(CURDATE())


I've already posted this question, but now I'm posting this with an edited version with the answers I got. Yet, I'm not getting the desired answer. What's wrong and what should I do?

Answer

try this one. I think you need a GROUP BY and I put in the WHERE to filter out the rows that aren't current month.

select pvs.contract_id,
       count(ir.contract_id) as counts,
       pvs.contract_date
from cbm_contract_period_value_split as pvs
left join cbm_contract_invoice_request as ir
on pvs.contract_id = ir.contract_id
and MONTH(ir.invoice_for_the_period_of_from_date) = MONTH(CURDATE())
WHERE MONTH(pvs.contract_date) = MONTH(CURDATE())
GROUP BY pvs.contract_id,pvs.contract_date

http://sqlfiddle.com/#!9/e5f08c/9

result

contract_id     counts  contract_date
1               3       May, 01 2016 00:00:00
3               0       May, 01 2016 00:00:00