MySQL - Union or join?

Question

I'm trying to collect statistic data in one SQL query for the convenience of having the date sorted automatically in a union. It is really only one table but I want to count different cases of data.

The table I have looks something like this:

ID   In          Wanted
441  2011-03-14  0
439  2011-03-14  1
442  2011-03-14  0
428  2011-03-13  1
431  2011-03-13  1
425  2011-03-11  0
423  2011-03-11  1
420  2011-03-09  1

I get close to the desired result with this query:

SELECT * FROM
(
  (SELECT date(In) n, count(date(In)) cntw, null cntl FROM items i WHERE Wanted=1 group by date(In))
union all
  (SELECT date(In) n, null cntw, count(date(In)) cntl FROM items i WHERE Wanted=0 group by date(In))
) Serie
Order by n DESC

But close isn't close enough :D The result i get is this:

n            cntw   cntl
2011-03-14   null   2
2011-03-14   1      null
2011-03-13   2      null
2011-03-11   null   1
2011-03-11   1      null
2011-03-09   1      null

What I want is to "blend" the results on the same line, by date:

n            cntw   cntl
2011-03-14   1      2
2011-03-13   2      null
2011-03-11   1      1
2011-03-09   1      null

As you can see there is only ONE row for each date. Actually the most perfect result would be to even have the missing dates in there too:

n            cntw   cntl
2011-03-14   1      2
2011-03-13   2      null
2011-03-12   null   null
2011-03-11   1      1
2011-03-10   null   null
2011-03-09   1      null

...but I guess this isn't possible.

Thank you!

Answer 1

select date(In) as n,
       sum(case when wanted = 1 then 1 else 0 end) as cntw,
       sum(case when wanted = 0 then 1 else 0 end) as cntl
    from items
    group by date(In)
    order by n desc

Answer 2

You'd use a LEFT JOIN using your n field to get the dates where you have stuff... Then you'd UNION this with a query that gives you the lines where there's nothing (the info you give above doesn't allow me to help in what query this would be :D).