[ciapug] SQL wiz?

[Claus]

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
> 
>