[ciapug] SQL wiz?

[Scott Phillips]

This isn't php, but php and SQL go hand-n-hand right?

Can someone explain to me how this sql query does what it does?  It is 
contained in a "news" class method that returns an array of news articles 
plus a count of comments associated with each.  I'm specifically interested 
in the LEFT JOIN.  I thought a LEFT JOIN returned rows where there is no 
match between tables?  (Or something like that.)  How does the "ON 
(c.news_id=n.news_id)" affect that?

         $sql = "SELECT
                     n.news_id,
                     n.news_title,
                     n.news_article,
                     n.news_release_dt,
                     COUNT(c.news_comment_id) AS cmt_cnt
                 FROM
                     ".PREFIX."_news n
                     LEFT JOIN ".PREFIX."_news_comments c ON 
(c.news_id=n.news_id)
                 WHERE
                     deleted=0
                     and status=1
                     and news_release_dt <= '".$sDate."'
                     and news_expire_dt > '".$sDate."'".$sFilter."
                 GROUP BY
                     n.news_id
                 ORDER BY
                     news_release_dt desc
                 LIMIT ".$iPage.", ".ROWCOUNT;


Thanks!

Oh, and Chris, I *may* have a good place for future meetings.  Let me check 
on availability and I will contact you with details.




Scott Phillips
Web Developer
Cowles Library, Drake University
(515) 271-2975