DatabaseMYSQL

MYQL Complex DB Queries – Get Marketers defaulting on project payments and terms

What we are learning from this?

Join, Subquery, Case

Statement:

We have projects in one table against which project payments and project payment terms are stored in separate tables as one to many mapping. Every project belongs have marketers assigned to in projects marketing spocs table. The requirement is to find the marketers whose projects are missing payment terms and payments so we can shoot mail to marketers to fill the data.

Projects Table

Projects Payments Table

Projects Payment Terms Table

Employees Table

Employees Details Attribute Table

Employees Details Table

Query Output

Explanation of code

In where clause below sql gives all projects which are having payment terms and project payments both added against it, we are using that as subquery to find the projects which are not in means the projects which are having either project payment terms or payment missing.

select ppt.projects_id
from projects_payments pp, projects_payments_terms ppt
WHERE pp.projects_id = ppt.projects_id

Below code in select is giving us the number of project payment terms and payments if its 0 means the terms and payments are missing.

CASE
WHEN (SELECTcount(*) FROM projects_payments pps WHERE pps.projects_id = p.id) THEN ‘Missing’
ELSE ‘Present’
ENDas paymentspresence,
CASE
WHEN (SELECTcount(*) FROM projects_payments_terms ppts WHERE ppts.projects_id = p.id) THEN ‘Missing’
ELSE ‘Present’
ENDas termspresence,

Below code in select is giving us the marketer email to send marketer the mail

(SELECT ed.value
FROM employee_details ed, employee_details_attributes eda
WHERE ed.employee_details_attributes_id = eda.id
AND eda.codename = ‘official-email-primary’
AND ed.employees_id = pms.employees_id) as marketeremail

About author

Love coding, crazy about logic, like solution architecting and solving problems. Thirsty for knowledge, challenges & most importantly innovation. One of the passionate brain behind SpryOX

Leave a Reply

Your email address will not be published. Required fields are marked *