List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:September 19 2002 5:05pm
Subject:Re: question about LEFT JOIN
View as plain text  

Michael Boudreau wrote:

> I can't tell if this is an SQL question or a MySQL question, so please 
> forgive me if it's not germane. I'm wondering whether I'm expecting 
> MySQL's "LEFT JOIN" to do something it can't, or if I just don't know 
> my SQL well enough.
>
> Given a database with these (partial) tables for manuscripts and editors:
>
> Mss
> ---
>   MsID     int(11) unsigned
>   EditorID int(11) unsigned
>   MsStatusCode int(3) unsigned  # code 101 = "active" MS
>
> People
> ------
>   PeopleID int(11) unsigned
>   Lastname varchar(40)
>   Databaserolecode int(3) unsigned  # codes 1, 2, and 3 are for "editors"
>
>
> Why wouldn't these two queries--to determine how many MSS are assigned 
> to each Editor--return the same number of rows? Shouldn't the "left 
> join" force the results to include *all* the editors, even if they 
> don't have active MSS assigned to them?


Not when you have "and m.msstatuscode=101"
NULL does NOT equal 101.

>
> (Using MySQL 3.23.37 on Solaris)
>
> mysql> select p.peopleid, p.lastname, count(m.msid) from People p left 
> join Mss m on p.peopleid=m.editorid where p.databaserolecode in 
> (1,2,3) group by p.peopleid;
> +----------+------------+---------------+
> | peopleid | lastname   | count(m.msid) |
> +----------+------------+---------------+
> |  1000066 | Barza      |          1656 |
> |  1000106 | Blacklow   |           280 |
> |  1000124 | Eliopoulos |             8 |
> |  1000509 | Goldstein  |           997 |
> |  1000513 | Gorbach    |           601 |
> |  1000524 | Graybill   |           307 |
> |  1000735 | Klein      |           245 |
> |  1001204 | Schooley   |            20 |
> |  1001345 | Tompkins   |           284 |
> |  1003540 | Yoshikawa  |            16 |
> |  1003736 | Karchmer   |           260 |
> |  1003984 | Meyer      |           343 |
> |  1005336 | Wanke      |           305 |
> |  1007198 | Sears      |           283 |
> +----------+------------+---------------+
> 14 rows in set (1.47 sec)
>
> mysql> select p.peopleid, p.lastname, count(m.msid) from People p left 
> join Mss m on p.peopleid=m.designatededitorid where p.databaserolecode 
> in (1,2,3) and m.msstatuscode=101 group by p.peopleid;
> +----------+-----------+---------------+
> | peopleid | lastname  | count(m.msid) |
> +----------+-----------+---------------+
> |  1000106 | Blacklow  |            26 |
> |  1000509 | Goldstein |             3 |
> |  1000513 | Gorbach   |            23 |
> |  1000524 | Graybill  |            27 |
> |  1000735 | Klein     |            19 |
> |  1001204 | Schooley  |            16 |
> |  1001345 | Tompkins  |            28 |
> |  1003736 | Karchmer  |            24 |
> |  1003984 | Meyer     |            27 |
> |  1005336 | Wanke     |            20 |
> |  1007198 | Sears     |            18 |
> +----------+-----------+---------------+
> 11 rows in set (1.10 sec)
>
>
>
> =================================================
> Michael Boudreau
> Senior Electronic Publishing Developer
> The University of Chicago Press
> 1427 E. 60th Street
> Chicago, IL 60637-2954
>
> phone: 773 753 3298
> fax: 773 753 3383
> =================================================
>
> ---------------------------------------------------------------------
> Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/           (the list archive)
>
> To request this thread, e-mail <mysql-thread120047@stripped>
> To unsubscribe, e-mail 
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


Thread
question about LEFT JOINUnknown Sender19 Sep
  • Re: question about LEFT JOINGerald Clark19 Sep