Conditional INNER JOIN or LEFT JOIN based on the joining condition

Question

I have a query

SELECT
  users.email AS email,
  addons.sku AS sku,
  addons.quantity as quantity,
 invoices.total as total

FROM addons

INNER JOIN users ON 1=1
  and users.id = addons.user_id

LEFT JOIN invoices ON 1=1
  AND invoices.user_id = users.id
  AND invoices.status != 3

Here is what I need to happen:

• if user doesn't have an invoice at all we should include them with NULL being returned in the total
• if user has an invoice in status != 3 we should include them
• if invoices exists and status = 3 we should exclude them.

So it's like I need both INNER JOIN and LEFT JOIN at the same time

How can I achieve that?

Answer 1

This is what you need:

SELECT
  users.email AS email,
  addons.sku AS sku,
  addons.quantity as quantity,
  invoices.total as total
FROM addons
INNER JOIN users
  ON users.id = addons.user_id
LEFT JOIN invoices 
  ON invoices.user_id = users.id
WHERE invoices.status IS NULL OR invoices.status != 3

Explanation:

• Users without invoices are included in the result, with "empty" invoice total. That's what the LEFT JOIN is for
• Users with invoice status != 3 are included, and so are their invoices. So, add that to the where clause (remember the status could be NULL because of the above LEFT JOIN)
• Users with invoice status = 3 are excluded. Do that with the WHERE clause