peterjc

Ok Barand. By the way, after i got the value and processed in php, NOw, i need to delete or remove all those records. I am using the sql like below, it is a bit slow, any suggestion to improve it? DELETE FROM table WHERE idtable IN ( SELECT * FROM ( SELECT idtable FROM table AS t INNER JOIN ( SELECT DISTINCT filename FROM table WHERE takendate <= '2012-05-03' ) AS X USING ( filename ) ) AS p ) Thanks in advance.

No freezing. Forgot the execution time, but i think less than 1 sec. By the way, regarding the sql SELECT * FROM table WHERE number IN (SELECT number FROM table WHERE date <= '2012-05-03') I think this sql is correct also right? why it is so slow? Is it because of using the "IN" clause that cause mysql to compare with too many records? Thanks.

Tried, and it work. but could i know why need to use UNION? because i tried without union, it will return the same result. SELECT t.id, t.date, t.number FROM tablename as t INNER JOIN ( SELECT DISTINCT number FROM tablename WHERE date <= '2012-05-03' ) as X USING (number) Thanks.

Tried and it will freeze my PC! If without the subquery, it's fast, but if add in the sub-query, it will freeze my PC There are around 100K of records. Just for info only, there are indexing for the table Anyway to solve this? Thanks.

Hi Here is example:of my question. id-----date------ number 1 2012-05-01 100 2 2012-05-01 101 3 2012-05-01 102 4 2012-05-02 103 5 2012-05-03 104 6 2012-05-04 105 7 2012-05-04 101 8 2012-05-04 102 9 2012-05-05 106 First, I want to select all records <= 2012-05-03 So, the result will look like below: id------date------ number 1 2012-05-01 100 2 2012-05-01 101 3 2012-05-01 102 4 2012-05-02 103 5 2012-05-03 104 But, now, i also want to include the records below. Because the number 101 and 102 also exist in the query above. id------date------ number 7 2012-05-04 101 8 2012-05-04 102 So, The final result that i want is: id------date------ number 1 2012-05-01 100 2 2012-05-01 101 3 2012-05-01 102 4 2012-05-02 103 5 2012-05-03 104 7 2012-05-04 101 8 2012-05-04 102 Could anyone please help how to write the sql query? There are about 100 thousand records in the database table. Hope you guys understand what i means, thanks in advance.

Thanks for that jazzman1. By the way, i don't really know how the "or" statement work. Example: We usually use: mysql_query($sql) or trigger_error() and so on could we actually use it for our own function as well? Example own_function() or trigger_error(); Thanks

Usually we would use mysql_query($sql) or trigger_error(mysql_error()); So instead of using trigger_error() directly, could i use our own function like below? mysql_query($sql) or showError(); function showError() { trigger_error(mysql_error()); die(); } The purpose is i want to end the script if errors were found. Thanks in advance.

Hi, Example from mysql.com SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id; SELECT * FROM table1 LEFT JOIN table2 ON table2.id= table1.id LEFT JOIN table3 ON table2.id=table3.id; So is it the same to write table1.id=table2.id or table2.id= table1.id Another example: SELECT * FROM table1 t1, table2 t2 WHERE t1.idtable1=t2.idtable1 SELECT * FROM table1 t1, table2 t2 WHERE t2.idtable1=t1.idtable1 Thanks