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 <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
