CODEWITHSUNDEEP
mysqlsqljoin
stackoverflow account
stackoverflow account

Reputation: 235

SQL : Getting last records for two conditions

i have two tables

  1. Companies
  2. company_payments

each company is doing two types of payments vat and witholding_tax, i am doing following query which returns me company's last payments for the current year

SELECT * FROM companies c 
JOIN ( SELECT MAX(id) max_id, company_id FROM company_payments ) 
c_max ON (c_max.company_id = c.id) 
JOIN company_payments cp ON (cp.id = c_max.max_id)
WHERE 
YEAR(cp.last_payment) = YEAR(CURDATE())

Below is the structure of my company_payments table

enter image description here

Now instead of returning one last payment i want to return last payment for payment type 'vat' and 'witholding_tax' both , if its not there would need an empty record ,

Could someone please advise me how can i achieve this

Upvotes: 1

Views: 190

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use a correlated subquery:

select cp.*
from company_payments cp
where cp.last_payment = (select max(cp2.last_payment)
                         from company_payments cp2
                         where cp2.company_id = cp.company_id and
                               cp2.payment_type = cp.payment_type
                        );

If you want to filter only on the most recent year, you can add the date filter to the outer query.

Upvotes: 1

Related Questions