mysql right join with group by problem

Question

I have 2 tables:

1. LandingPages - contain landing pages per campaign.

2. Reports - contain hits and conversion per landing page.

I try to do query that bring the sum of hits and conversion per landing page,

But i want that if the landing page has not received any hits and conversion (and not show in reports table) then i want that return 0 as result.

What i do until now is:

SELECT l.LandingPageId, SUM(Hits) AS Hits, SUM(PixelSum) AS Conversion 
FROM Report c
RIGHT JOIN LandingPages l ON(c.LandingPageId = l.LandingPageId )
WHERE c.CampaignId = x  
AND DayDate > 'y'
GROUP BY c.LandingPageId

The problem is that i get only rows with the landing page that exist in reports table and pass the date 'y',

(e.g : i get only 2 rows of landing page , but there is 4 landing page

if i run this query i get 4 results

SELECT l.LandingPageId FROM LandingPages l WHERE l.CampaignId = x 

)

not the all landing page (with 0 value),

how can i get this to work like i want, give me also the landing page that not in report table or in the table but in old date ?

thanks.

update :

I was helped a lot for your answers, I got partial solution that work for me only if the landing page not exist at all in report table, but if it is exist but the date clause not match it not appear :

the partial solution query :

 SELECT l.LandingPageId, IFNULL(SUM(Hits),0) AS Hits, IFNULL(SUM(PixelSum),0)  AS Conversion
    FROM LandingPages l
    LEFT JOIN Report c  ON( l.LandingPageId = c.LandingPageId) 
    WHERE (l.CampaignId = x OR  l.CampaignId IS NULL) 
    AND (DayDate > 'y' OR DayDate IS NULL)
    GROUP BY l.LandingPageId

I still need your help !

thanks!

Answer 1

Okay. When I run the following I get the result from below. Is that what you want?

drop table landingpages;
create table landingpages (campaignid number, landingpageid number,  daydate number);

insert into landingpages values (1,100,20);
insert into landingpages values (1,101,21);
insert into landingpages values (2,102,20);
insert into landingpages values (2,103,21);

drop table report;
create table report (campaignid number, landingpageid number, hits number, pixelsum number);

insert into report values (1,100, 2, 1 );
insert into report values (2,102, 20, 21 );
insert into report values (2,103, 30, 31 );

commit;

SELECT c.LandingPageId, SUM(Hits) AS Hits, SUM(PixelSum) AS Conversion  
    FROM landingpages c 
    LEFT JOIN report l ON(c.LandingPageId = l.LandingPageId ) 
    WHERE c.CampaignId = 1   
    AND DayDate > 19 
    GROUP BY c.LandingPageId 


LANDINGPAGEID       HITS CONVERSION
------------- ---------- ----------
          100          2          1
          101                      


2 rows selected.

I hope this is what you need. I ran the above on Oracle but it should be no real difference in mySQL as this is all standard query language.