mysql right join with group by problem

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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.