2008/12/21 Rachmat Febfauza <mathi...@yahoo.com>: > > It looks like you are using the sqlite3 shell, so experiment with .separator > > Have you used .help? > > Rgds, > Simon > > > yes i am using sqlite3 shell, i mean not to make display like mysql does, but > the difference column that i want like mysql does. > > take a look at this different > A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649 > (this is done with sqlite) > > A1221 SMALL FOOD CAKE HOMS 1 2007-5-6 11:31:57 2007-5-6 > 11:31:57 0 (this is done with mysql) > > why in sqlite we got (649) : 2007-05-06 11:31:57|2007-05-06 11:42:46|649 > and in mysql we got (0) : 2007-5-6 11:31:57 2007-5-6 11:31:57 0 > > or i think the reason is like i found in mysql documentation like this : > "If you use LEFT JOIN to find rows that do not exist in some table and you > have the following test: col_name IS NULL in the WHERE part, where col_name > is a column that is declared as NOT NULL, MySQL stops searching for more rows > (for a particular key combination) after it has found one row that matches > the LEFT JOIN condition. " > > i also have test with left join but the result don't like mysql does. > > sory for my less knowledge > > thanks >
Without the group by clause the result set is: sqlite> select awal1.Code, awal1.Level, awal1.Category, awal1.Product, awal1.Location, awal1."Begin",akhir1."End", strftime("%s",akh ir1."End")-strftime("%s",awal1."Begin") as Difference from awal1, akhir1 where awal1.Code = akhir1.Code and akhir1.Category like awa l1.Category || '%' and awal1.Product = akhir1.Product and awal1.Location = akhir1.Location and akhir1."End" >= awal1."Begin"; A1220 SMALL FOOD MARGARINE HOMS 1 2007-05-06 11:42:46 2007-05-06 11:42:46 0 A1221 SMALL FOOD CAKE HOMS 2 2007-05-06 11:31:57 2007-05-06 11:31:57 0 A1221 SMALL FOOD CAKE HOMS 1 2007-05-06 11:31:57 2007-05-06 11:31:57 0 A1221 SMALL FOOD CAKE HOMS 1 2007-05-06 11:31:57 2007-05-06 11:42:46 649 A1221 SMALL FOOD CAKE HOMS 1 2007-05-06 11:42:46 2007-05-06 11:42:46 0 A1222 SMALL FOOD WAFER HOMS 2 2007-05-06 11:20:34 2007-05-06 11:31:57 683 A1222 SMALL FOOD WAFER HOMS 1 2007-05-06 11:20:34 2007-05-06 11:31:57 683 A1222 SMALL FOOD WAFER HOMS 1 2007-05-06 11:20:34 2007-05-06 11:42:46 1332 A1222 SMALL FOOD WAFER HOMS 1 2007-05-06 11:42:46 2007-05-06 11:42:46 0 A1236 MEDIUM FOOD SNACK HOMS 2 2007-05-06 10:48:57 2007-05-06 11:19:21 1824 A1236 MEDIUM FOOD SNACK HOMS 1 2007-05-06 10:48:57 2007-05-06 11:19:25 1828 A1269 SMALL CLOTHES BELT HOMS 3 2007-05-07 17:28:25 2007-05-07 17:28:27 2 The group by clause combines rows A1221 SMALL FOOD CAKE HOMS 1 2007-05-06 11:31:57 2007-05-06 11:31:57 0 A1221 SMALL FOOD CAKE HOMS 1 2007-05-06 11:31:57 2007-05-06 11:42:46 649 into 1 row. The values in the columns not included in the group by clause ("Begin", "End" and Difference) could be from any of the combined rows (which rows is not, I believe, specified in any standard). MySql and Sqlite seem to result in different selections. If you want specific rows, then you need to modify the query to control the data selection. In this case it appears that: select awal1.Code, awal1.Level, awal1.Category, awal1.Product, awal1.Location, awal1."Begin", min( akhir1."End" ), min( strftime("%s",akhir1."End")-strftime("%s",awal1."Begin") ) as Difference from awal1, akhir1 where awal1.Code = akhir1.Code and akhir1.Category like awal1.Category || '%' and awal1.Product = akhir1.Product and awal1.Location = akhir1.Location and akhir1."End" >= awal1."Begin" group by awal1."Begin", awal1.Code, awal1.Category, awal1.Product, awal1.Location; gives the result you want. Rgds, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users