CODEWITHSUNDEEP
mysqljoin
ekstro
ekstro

Reputation: 481

MySQL join only selected rows

SELECT invoices.number_formatted, SUM(invoices_elements.netto)
FROM invoices
LEFT JOIN invoices_elements ON invoices_elements_code_invoices_id = invoices_id
WHERE invoices_enable = 1
AND invoices_elements_enable = 1
GROUP BY invoices_elements_code_invoices_id

If table "invoices_elements" doesn't have any rows with "invoices_elements_enable = 1" this query return NULL - but i want "number formatted". So i do this:

SELECT SUM(netto)
FROM (invoices)
LEFT JOIN (SELECT * FROM invoices_elements WHERE invoices_elements_enable = 1) ON invoices_elements_code_invoices_id = invoices_id
WHERE invoices_enable = 1
GROUP BY invoices_elements_code_invoices_id

... and this of coz works. But - is better way to do it?

Upvotes: 1

Views: 1096

Answers (1)

Martin Smith
Martin Smith

Reputation: 453192

You can do 

SELECT SUM(netto)
FROM   invoices
       LEFT JOIN invoices_elements
         ON invoices_elements_code_invoices_id = invoices_id
            AND invoices_elements_enable = 1
WHERE  invoices_enable = 1
GROUP  BY invoices_elements_code_invoices_id

Note the restriction invoices_elements_enable = 1 is in the ON clause to avoid converting the query into an inner join.

Upvotes: 2

Related Questions