MySQL where condition but not limited by it

Question

Hello. I would like to run a query on my database like this:

SELECT SUM( t1.value ) AS total1, SUM( t2.value ) AS total2, SUM( t3.value ) AS total3, SUM( t4.value ) AS total4
FROM pay1 t1, pay2 t2, pay3 t3, pay4 t4
WHERE t1.date = '2010-04-29'
AND t2.date = '2010-04-29'
AND t3.date = '2010-04-29'
AND t4.date = '2010-04-29'

I am generating a report on payments and I would like to see a total of payments from each table based on the matching date. The problem is that some of the tables would not meet the condition of date and I want them to show up with 0 value if not. Currently, if any of the tables does not match the date, I get 0 results. I want to display value of 0 anywhere the date is not met and other fields should appear with the found values.

The perfect operand for me would be "ANDOR" so that it won't be limited by any date that doesn't math in any table. Unfortunately, ANDOR does not exist as I am aware of so what should I do ?

Can anyone help ? Thanks.

Answer 1

It sounds like you want to use an outer join, instead of an inner join, which is the default.

An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).

-Wikipedia

Answer 2

You want

SELECT
  (SELECT SUM( value ) FROM t1 WHERE date = '2010-04-29') AS total1,
  (SELECT SUM( value ) FROM t2 WHERE date = '2010-04-29') AS total2,
  (SELECT SUM( value ) FROM t3 WHERE date = '2010-04-29') AS total3,
  (SELECT SUM( value ) FROM t4 WHERE date = '2010-04-29') AS total4