Join/Subquery

[Anyaer]

I have three tables: Clicks, Emails, and Calls. I need to join these three together, making sure that every  time an entry exists in one for a given date it's included in the results, even if the other two tables don't have it. Basically, I'm doing this step by step. In all three tables, the entries are stored by date. I'm calculating them on a monthly basis.

 

I hit on subqueries on a possible way to do this simply but I've never used them before. Here's what I have so far, just working with two of the tables:

 

SELECT DATE_FORMAT(cdate, '%Y-%m') Date, source Source, sum(imprss) Impressions, sum(cost) Cost, sum(clicks) Clicks
FROM table1 p RIGHT JOIN
    (SELECT DATE_FORMAT(cdate, '%Y-%m') Date, COUNT(company_id) Calls
    FROM table2
    WHERE `cdate` between '2006-01-01' and '2006-12-31' AND
    company_id = 2
    GROUP BY MONTH(cdate)) AS l
ON p.cdate = l.Date
WHERE `cdate` between '2006-01-01' and '2006-12-31' AND
company_id = 2
GROUP BY MONTH(cdate)

 

Either of the queries if run separately return their proper sets. But this returns an empty set, even though the two sets that should be returned do overlap. If I run a LEFT JOIN, it returns the results as though I had run just the outer query.

[fenway]

I only see 2 tables... and right joins are evil.

[btherl]

The logical explanation is that the inner query returns no matching results.  Have you tried the inner query on its own?

 

I suspect the problem is that l.Date is reformatted as a string, but p.cdate is not.