That's fantastic! Thanks. I'm gonna have to read through this again and see how you did it exactly so I can apply it to the other problems I'm having, but this is going to reduce my code by probably 15-20 lines *per instance* all told, as well as eliminating a function call (I needed an extra function call to open a unique MySQL connection so that all temp. tables could be kept seperate.)
Dan > -----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 ____________________ BYU Unix Users Group http://uug.byu.edu/ ___________________________________________________________________ List Info: http://uug.byu.edu/cgi-bin/mailman/listinfo/uug-list
