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开发者_如何学C NULL) 
    AND (DayDate > 'y' OR DayDate IS NULL)
    GROUP BY l.LandingPageId

I still need your help !

thanks!

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.

First, you did not tell us in which table Hits, PixelSum, or DayDate are stored. The ? represents that fact in my query. Obviously, the ? will need to replaced with the proper alias. However, I assumed that DayDate came from the Report table given that you later mentioned a problem if the date criteria did not match.

In short, you need to apply that criteria in the ON clause of the Left Join. The ON clause criteria is applied before it is joined to the LandingPages table. Thus, Campaigns <> X will be filtered out in addition to DayDate values <= 'y' (Btw, what is the data type of DayDate? DayDate > 'y' looks suspicious to me) before the Report table is then joined to the LandingPages table.

In addition, you should consider using Coalesce instead of IsNull since Coalesce is the ISO standard.

Select L.LandingPageId
    , Coalesce( Sum( ?.Hits ), 0 ) As Hits
    , Coalesce( Sum( ?.PixelSum ), 0 ) As Conversion
From LandingPages As L
    Left Join Report As R
        On R.LandingPageId = L.LandingPageId
            And R.CampaignId = X
            And ( R.DayDate > 'y' Or R.DayDate Is Null )
Group By L.LandingPageId

For more information on Left Joins, here is a visual representation.

It's b/c of the RIGHT JOIN. Rerun it as:

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

You have this: WHERE c.CampaignId = x this means that if the landing page has not received any hits and conversion (and not show in reports table), the landing page will never show up in the results, although you use right join. Your c.CampaignId would be null for those landing pages and c.CampaignId = x would be false.

try:

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  or c.CampaignId is null)  
AND DayDate > 'y'
GROUP BY l.LandingPageId

I also group by l.LandingPageId because for landing pages with no reports, c.LandingPageId is null.

i take the idea of thomas , and with little improve its work !

the query :

Select L.LandingPageId
    , Coalesce( Sum( R.Hits ), 0 ) As Hits
    , Coalesce( Sum( R.PixelSum ), 0 ) As Conversion
From LandingPages As L
    Left Join Report As R
        On R.LandingPageId = L.LandingPageId
            And L.CampaignId = X
            And R.DayDate > 'y' 
WHERE L.CampaignId = X
Group By L.LandingPageId

Some problems I see...

• please prefix your columns (i.e., l.fieldname, c.fieldname) in all cases, so I can tell which table you're getting them from. Anyway, I made some sample code for you below, but am not sure about it 100% since i didn't always know the table, which is important with a RIGHT JOIN, so you may need to adjust it.
• when you set criteria (WHERE c.CampaignID = something) on a right-joined table, you are turning it into an INNER JOIN. If you want to avoid this, then add "...or c.CampaignID is null). Because the idea of the RIGHT join is, IF there's a campaignID, you want it to be 'x', but If there's no campaign, that's ok too. (right?)

you can't sum nulls, so i added coalesce to change nulls to zero.

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