[ciapug] SQL wiz?

[Scott Phillips]

Thanks for the tutorial, Claus!  Once upon a time I knew what all those 
join types did.  If you don't use it, you loose it I guess.  I didn't 
realize a left join without "on" would return every combination like 
that.  (Pretty much useless isn't it?)  That's what was throwing me.  Makes 
sense now.

Thanks again.

At 10:19 AM 2/15/2005 -0600, you wrote:
>The "ON" statement specifies on what columns the two table are being 
>joined.  You really need to learn SQL to understand this stuff.  Here's a 
>small crappy lesson.  Assume you have table_a and table_b like this:
>
>table_a
>id | name
>---+-----
>1  | Arnold
>2  | John
>4  | Bob
>5  | Jim
>
>table_b
>id | grade
>---+------
>1  | B-
>2  | A+
>2  | C
>3  | D
>5  | A
>
>Now you are joining the tables on the common column "id" like this:
>
>select a.id,
>        a.name,
>        b.grade
>from   table_a a
>        left join table_b b on (a.id = b.id)
>order by a.id
>
>The result will be:
>
>id | name   | grade
>---+--------+-------
>1  | Arnold | B-
>2  | John   | A+
>2  | John   | C
>4  | Bob    | <null>
>5  | Jim    | A
>
>Note three things:
>- the record 2 has two rows since there are two rous in table_b
>- the record 4 has null as a grade since no entry was found in table_b
>   (this behavior is due to the "left join")
>- the record 3 does not appear since it has no entry in table_a which is
>   the driving table (due to the "left join")
>
>If you don't specify the on statement then all rows from one table will be 
>joined with all rows from the other table and you get a huge mess like this:
>
>id | name   | grade
>---+--------+-------
>1  | Arnold | B-
>1  | Arnold | A+
>1  | Arnold | C
>1  | Arnold | D
>1  | Arnold | A
>2  | John   | B-
>2  | John   | A+
>2  | John   | C
>2  | John   | D
>2  | John   | A
>4  | Bob    | B-
>4  | Bob    | A+
>4  | Bob    | C
>4  | Bob    | D
>4  | Bob    | A
>5  | Jim    | B-
>5  | Jim    | A+
>5  | Jim    | C
>5  | Jim    | D
>5  | Jim    | A
>
>So much for the join, now on with the count and group statement.  First 
>and foremost I'm supprised that the statement works without having to 
>specifiy also the columns news_title, news_article, and news_release_dt in 
>the "group by" statement.  This might be a special feature that mysql has 
>but it will most likely not work if you use that statement with another 
>database.
>
>Assume you have the following table:
>
>table_c
>id | name   | grade
>---+--------+-------
>1  | Arnold | B-
>2  | John   | A+
>2  | John   | C
>5  | Jim    | A
>
>To count how many grades each person has you'd use the following statement:
>
>select c.id,
>        c.name,
>        count(c.grade)   -- or you could use "count(*)"
>from table_c c
>group by c.id,
>          c.name
>order by c.id
>
>Basically this statements sellects all rows and groups them uniquely by 
>the columns specified and then counts how many rows of that specific group 
>exists.  Instead of count() you also could use max(), min(), sum(), 
>average(), ... to do other neat stuff with the group statement.
>
>   Claus
>
>
>On 2/15/2005 9:29 AM, Scott Phillips wrote:
>>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
>>_______________________________________________
>>ciapug mailing list
>>ciapug@cialug.org
>>http://cialug.org/mailman/listinfo/ciapug
>
>_______________________________________________
>ciapug mailing list
>ciapug@cialug.org
>http://cialug.org/mailman/listinfo/ciapug

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