2008/12/21 Rachmat Febfauza <[email protected]>:
>
> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
