Copyright (C) 2000-2012 |
GNU Info (mysql.info)No matching rowsSolving Problems with No Matching Rows -------------------------------------- If you have a complicated query that has many tables and that doesn't return any rows, you should use the following procedure to find out what is wrong with your query: 1. Test the query with `EXPLAIN' and check if you can find something that is obviously wrong. Note: `EXPLAIN'. 2. Select only those fields that are used in the `WHERE' clause. 3. Remove one table at a time from the query until it returns some rows. If the tables are big, it's a good idea to use `LIMIT 10' with the query. 4. Do a `SELECT' for the column that should have matched a row against the table that was last removed from the query. 5. If you are comparing `FLOAT' or `DOUBLE' columns with numbers that have decimals, you can't use `='! This problem is common in most computer languages because floating-point values are not exact values: mysql> SELECT * FROM table_name WHERE float_column=3.5; -> mysql> SELECT * FROM table_name WHERE float_column between 3.45 and 3.55; In most cases, changing the `FLOAT' to a `DOUBLE' will fix this! 6. If you still can't figure out what's wrong, create a minimal test that can be run with `mysql test < query.sql' that shows your problems. You can create a test file with `mysqldump --quick database tables > query.sql'. Open the file in an editor, remove some insert lines (if there are too many of these), and add your select statement at the end of the file. Test that you still have your problem by doing: shell> mysqladmin create test2 shell> mysql test2 < query.sql Post the test file using `mysqlbug' to <mysql@lists.mysql.com>. automatically generated by info2www version 1.2.2.9 |