CODEWITHSUNDEEP
mysqlsqlwhere-clause
Milos Cuculovic
Milos Cuculovic

Reputation: 20223

Mysql - How to make an addition on two fields from distinct tables in where clause

I have two tables

Invoices
  id
  discount
  voucher_id
  ...

Vouchers
  id
  discount  
  ...

My goal is not to get the count of invoices where the total discount is equal to 100.

I tried 

SELECT count(*) 
FROM invoices 
LEFT JOIN vouchers ON invoices.voucher_id = vouchers.id
WHERE (invoices.discount + vouchers.discount) = 100

But this will return an empty result, and in my DB, I have fields that respects that where clause.

Upvotes: 0

Views: 26

Answers (1)

Tom Mac
Tom Mac

Reputation: 9853

Your query will only bring back invoices that have a voucher discount because you have included vouchers.discount in your WHERE clause.

Assuming that not all invoices have a voucher_id value (from the fact that you are using a LEFT JOIN instead of an INNER JOIN) & also assuming that an invoice can only ever have one voucher.....

select count(*) from
 (
  SELECT invoices.discount+ifnull(vouchers.discount,0) as total_discount
  FROM invoices 
  LEFT JOIN vouchers ON invoices.voucher_id = vouchers.id
 ) t
where total_discount = 100;

Upvotes: 1

Related Questions