How to fill a no-value colum with a default value in result set (MySQL)

I have a query like follows:

SELECT table1.time, table1.co, table2.co_ro
FROM(
SELECT p1.time, SUM(p1.kpi_0) AS co
FROM hourly_history p1
WHERE p1.type_id=3
GROUP BY p1.time) AS table1,(
SELECT p2.time, SUM(p2.kpi_0) AS co_ro
FROM hourly_histo开发者_运维技巧ry p2
WHERE p2.type_id=5
GROUP BY p2.time) AS table2
WHERE table1.time=table2.time

If data exist for type_id=3 and does not exist for type_id=5 for a specific time value (let's say 12-10-2011 12:00:00), row of that time doesn't exist in result set as expected.

I need a query that returns the row for that specific time even no data exist for type_id=5. How can I write such a query?

Thanks in advance.

---

This concept should work (although I have not tested these queries):

SELECT t1, sum(co), sum(co_ro)
FROM
(
  (
    SELECT p1.time AS t1, SUM(p1.kpi_0) AS co, 0 AS co_ro
    FROM hourly_history p1
    WHERE p1.type_id=3
    GROUP BY p1.time
  )

  UNION ALL

  (
    SELECT p2.time AS t1, 0 AS co, SUM(p2.kpi_0) AS co_ro
    FROM hourly_history p2
    WHERE p2.type_id=5
    GROUP BY p2.time
  )
)

GROUP BY t1;

This should be even simpler:

SELECT t1, sum(co), sum(co_ro)
FROM
(
  (
    SELECT p1.time AS t1, p1.kpi_0 AS co, 0 AS co_ro
    FROM hourly_history p1
    WHERE p1.type_id=3
  )

  UNION ALL

  (
    SELECT p2.time AS t1, 0 AS co, p2.kpi_0 AS co_ro
    FROM hourly_history p2
    WHERE p2.type_id=5
  )
)

GROUP BY t1;

---

The basic way to do this is to create a table with all the times that should exist, and then left join against that. Then your missing values become nulls, which you can use COALESCE to fill with defaults.

The table with the times doesn't have to be a base table. It can be built by joining together a days, hours, and minutes table, either as a view or as part of the query.

Though, in your example query, you might get what you want just by switching from an inner join to a left join. MySQL doesn't do full outer join, unfortunately.