display all cleaners using Join

[Jassim Rahma]

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 XoomPage[^]

Technology News @ www.JassimRahma.com

[Lost User]

Jassim Rahma wrote:

I want to display ALL cleaners even if no entry in the job_order table

..then don't use an INNER join.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

[Jassim Rahma]

but I want to get order details as well?!

Technology News @ www.JassimRahma.com

[Lost User]

Jassim Rahma wrote:

but I want to get order details as well?!

You'd also want the cleaners that don't have an order detail?

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

[Jassim Rahma]

yes.. I want to display all if cleaner has order then it will display the SUM but if not then I want to display ZERO, null, blank or whatever..

Technology News @ www.JassimRahma.com

[Bernhard Hiller]

What about a LEFT JOIN?

[David Mujica]

Look into LEFT OUTER joins

[Lost User]

Visual Representation of SQL Joins[^] :)

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]