This is operating as it should. The order of the left joins sets the precedence of the selection.
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 04, 2001 10:55 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Select with Left Joins Perhaps this is obvious, but I have a SELECT (with many LEFT JOINs) that I would expect to return results, but it does not. If I make one change in the order in which tables are used, the SELECT works: The SQL that doesn't work is: ----------------------------------- SELECT T1.patentNumber, T1.title, T2.ipvMap, T3.claimCode, T2.numOfFC, T2.numOfBC FROM usptoDataTable AS T1 LEFT JOIN ipvDataTable AS T2 USING (patentNumber) LEFT JOIN clientDataTable AS T3 USING (patentNumber) LEFT JOIN iPlanetDataTable AS T4 USING (patentNumber) WHERE T4.sunPatentNumber LIKE '%P%' LIMIT 10 ----------------------------------- No rows are returned even though there are lots of rows in table T4 that fit the criteria. Changing the order of the LEFT JOIN of T3 and T4 returns 10 rows. The SQL that works is: ----------------------------------- SELECT T1.patentNumber, T1.title, T2.ipvMap, T3.claimCode, T2.numOfFC, T2.numOfBC FROM usptoDataTable AS T1 LEFT JOIN ipvDataTable AS T2 USING (patentNumber) LEFT JOIN iPlanetDataTable AS T4 USING (patentNumber) <---- order of these two is swapped but alias name unchanged LEFT JOIN clientDataTable AS T3 USING (patentNumber) <---- order of these two is swapped but alias name unchanged WHERE T4.sunPatentNumber LIKE '%P%' LIMIT 10 ----------------------------------- Is this a bug or user error? Paul Albrecht --------------------------------------------------------------------- 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
