-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Daniel Crookston wrote: | It wouldn't work because with a join like that, you'll be getting procedures | from other doctors (in fs) matching up with procedures in p. So if *any* | doctor had a procedure defined in fs, it'd get matched up, regardless of | whether the doctor you're working with has it set up. | | What's needed is a way to get rid of all records in fs where fs.doctor_id != | $doctor_id *before* the join occurs. | | Dan
Actually, you wouldn't. From the MySQL online documentation[1]:
"A LEFT JOIN B join_condition in MySQL is implemented as follows:
~ * The table B is set to be dependent on table A and all tables ~ that A is dependent on. ~ * The table A is set to be dependent on all tables (except B) ~ that are used in the LEFT JOIN condition. ~ * The LEFT JOIN condition is used to decide how we should ~ retrieve rows from table B. (In other words, any condition ~ in the WHERE clause is not used). ~ * All standard join optimizations are done, with the exception ~ that a table is always read after all tables it is dependent ~ on. If there is a circular dependence then MySQL will issue ~ an error. ~ * All standard WHERE optimizations are done. ~ * If there is a row in A that matches the WHERE clause, but ~ there wasn't any row in B that matched the ON condition, then ~ an extra B row is generated with all columns set to NULL. ~ * If you use LEFT JOIN to find rows that don't exist in some ~ table and you have the following test: column_name IS NULL in ~ the WHERE part, where column_name is a column that is declared ~ as NOT NULL, then MySQL will stop searching after more rows ~ (for a particular key combination) after it has found one row ~ that matches the LEFT JOIN condition."
You'll see in bullet 3, only rows from B matching the LEFT JOIN condition (the ON clause) are used in the join. So by including doctor_id!=2 in the ON clause, only fee_schedules for doctors with id not equal to 2 are included in the left join.
I tested this with the attached DB dump and the following two queries returned the exact same set.
- -------- Yours: - -------- mysql> create temporary table tmp_fs ~ -> select * from fee_schedules where doctor_id=2; Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0
mysql> select description from procedures left join tmp_fs ~ -> on tmp_fs.procedure_id = procedures.id ~ -> where tmp_fs.id is null; +-------------+ | description | +-------------+ | A | | B | | C | | E | +-------------+ 4 rows in set (0.00 sec)
- -------- Mine: - -------- mysql> select description from procedures p left join fee_schedules fs ~ -> on fs.procedure_id=p.id and fs.doctor_id=2 ~ -> where fs.id is null; +-------------+ | description | +-------------+ | A | | B | | C | | E | +-------------+ 4 rows in set (0.00 sec)
Hope this helps (working through it has helped me understand left joins quite a bit better too :).
Jacob Fugal
[1] http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#LEFT_JOIN_optimisation -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQFABCyW/PO7QochUiQRAnOLAKCOCH92FZzEtItlgO3J5l4n3NuWFQCguutE YINrk+RF6VPDae+eT1VYWHk= =y9/r -----END PGP SIGNATURE-----
-- MySQL dump 9.09 -- -- Host: localhost Database: test -- ------------------------------------------------------ -- Server version 4.0.16
-- -- Table structure for table `fee_schedules` -- CREATE TABLE fee_schedules ( procedure_id int(11) unsigned NOT NULL default '0', doctor_id int(11) unsigned NOT NULL default '0', id int(11) unsigned NOT NULL auto_increment, PRIMARY KEY (id), KEY procedure_id (procedure_id), KEY doctor_id (doctor_id) ) TYPE=MyISAM; -- -- Dumping data for table `fee_schedules` -- INSERT INTO fee_schedules VALUES (1,1,1); INSERT INTO fee_schedules VALUES (2,3,2); INSERT INTO fee_schedules VALUES (3,1,3); INSERT INTO fee_schedules VALUES (4,2,4); INSERT INTO fee_schedules VALUES (5,3,5); INSERT INTO fee_schedules VALUES (6,2,6); INSERT INTO fee_schedules VALUES (7,2,7); -- -- Table structure for table `procedures` -- CREATE TABLE procedures ( id int(11) unsigned NOT NULL auto_increment, description text, PRIMARY KEY (id) ) TYPE=MyISAM; -- -- Dumping data for table `procedures` -- INSERT INTO procedures VALUES (1,'A'); INSERT INTO procedures VALUES (2,'B'); INSERT INTO procedures VALUES (3,'C'); INSERT INTO procedures VALUES (4,'D'); INSERT INTO procedures VALUES (5,'E'); INSERT INTO procedures VALUES (6,'F'); INSERT INTO procedures VALUES (7,'G');
____________________ BYU Unix Users Group http://uug.byu.edu/ ___________________________________________________________________ List Info: http://uug.byu.edu/cgi-bin/mailman/listinfo/uug-list
