display all cleaners using Join Pin

[jrahma]

Hi,

I am using the following code to populate all cleaners and display the income from every cleaner. It works fine when there is a entry for the cleaner in the job_order table but if no entry then it will not display the cleaner.

I want to display ALL cleaners even if no entry in the job_order table but of course with ZERO income.

SELECT
cleaners.cleaner_id,
cleaners.cleaner_name,
cleaners.date_of_join,
SUM(invoices.invoice_net_amount) as total_income
FROM
cleaners
INNER JOIN job_orders ON job_orders.cleaner_id = cleaners.cleaner_id
INNER JOIN invoices ON invoices.job_order_id = job_orders.job_order_id
GROUP BY
cleaners.cleaner_id
ORDER BY cleaners.cleaner_name;

Jassim Rahma

[bradgrafelman]

Use a LEFT JOIN rather than an INNER JOIN when you join cleaners with job_orders.