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