Count number of matching entries in separate Table with one query?

Schweppesale · #1 (**permalink**) 12-21-09, 20:25

Hi, I have the following query.

Code:

  $query = "SELECT #__jt_banners.id
                , #__jt_banners.title
                , #__jt_banners.img_location
                , #__jt_banners.impressions
                , #__jt_banners.start_date
                , #__jt_banners.expiration_date
                , #__jt_banners.published 
          FROM #__jt_banners";

I need to count the number of times each (#__jt_banners.id = #__jt_banner_clicks.banner_id)

Some banner entries will have clicks, some won't. I'd like to limit this to one query if possible, how should I go about doing this?

r937 · #2 (**permalink**) 12-21-09, 23:24

Code:

SELECT b.id
     , b.title
     , b.img_location
     , b.impressions
     , b.start_date
     , b.expiration_date
     , b.published 
     , COALESCE(bc.bclicks,0) AS bclicks
  FROM #__jt_banners AS b
LEFT OUTER
  JOIN ( SELECT banner_id
              , COUNT(*) AS bclicks
           FROM #__jt_banner_clicks
         GROUP
             BY banner_id ) AS bc
    ON bc.banner_id = b.id

Schweppesale · #3 (**permalink**) 12-22-09, 01:29

didn't work

r937 · #4 (**permalink**) 12-22-09, 02:55

Quote:

Originally Posted by Schweppesale

didn't work

and i'm supposed to guess why?

Schweppesale · #5 (**permalink**) 12-22-09, 05:09

Code:

SELECT #__jt_banners.id
                , #__jt_banners.title
                , #__jt_banners.img_location
                , #__jt_banners.impressions
                , #__jt_banners.start_date
                , #__jt_banners.expiration_date
                , #__jt_banners.published
              
                , #__jt_banner_clicks.banner_id
                , COUNT(#__jt_banner_clicks.banner_id) as bann_cnt
          FROM #__jt_banners
            LEFT JOIN #__jt_banner_clicks ON
           (#__jt_banners.id = #__jt_banner_clicks.banner_id)
           GROUP BY #__jt_banners.id

works now. don't worry about it

r937 · #6 (**permalink**) 12-22-09, 08:25

please, do me the courtesy, since i took the time to help you for free, please at least explain why my query "didn't work"

because i'm pretty sure it did

Internet Services

Web Development

Digital Marketing

Consumer Tech