local cServer := 'localhost'
local cUser := 'root'
local cPassword := '123456'
local cDbName := 'easyfo'
oSvr := TMySqlServer():New(cServer,cUser,cPassword)
oSvr:SelectDb( cDbName )
oDb1 := oSvr:Query("SELECT * FROM ccrtbl LEFT JOIN ccrgst ON ccrtbl.tbl_gstno=ccrgst.gst_intno")
Last edited by dutch on Wed Jun 24, 2015 4:49 am, edited 2 times in total.
Regards,
Dutch
FWH 19.01 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio
FWPPC 10.02 / Harbour for PPC (FTDN)
ADS V.9 / MySql / MariaDB
R&R 12 Infinity / Crystal Report XI R2
(Thailand)
I've read the JOIN statement from http://www.w3schools.com, it is quite simple.
I can ::Query one by one in a few seconds without problem. I try to JOIN (SET RELATION TO) for CCRTBL and CCRGST. It doesn't show (wait for long time more than 5 minutes and I terminate in Task manager).
I'm not quite sure, what is the problem? Can I use this statement with MySql.Lib, LibMySql.Lib?
Thanks for kind help.
Regards,
Dutch
FWH 19.01 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio
FWPPC 10.02 / Harbour for PPC (FTDN)
ADS V.9 / MySql / MariaDB
R&R 12 Infinity / Crystal Report XI R2
(Thailand)
1.- Are the tables 1 to 1.... 1 Record in ccrtbl and 1 record in ccrgst ?
2.- Are the tables master to child.... 1 Record in ccrtbl and many records in ccrgst ?
3.- How many fields are there in the 2 tables ?
4.- Both tables have an Index with the relation field ?
Answers to possible errors...
1.- Here is the most perfect relation... there must be no error and the query is very fast... if there is 1 to 1 relation
2.- Here you must thing backwards.... Select on the child table... the numbers of resulting rows must be the initial number of records in that table. So very fast indeed.... BUT. If you do it all the way around you may have a big resulting query, then a slow query result.
3.- If you have 50 fields on the tables, you must assume that you are going to get a 100 fields query. Then the idea is to reduce it to have only the required fields to work in the query. Also try to use alias.
Ex.
Select chi.code, chi.number, chi.ammount, mas.date, mas.client from child as chi left join master as mas on chi.CodeMas=mas.Code
4.- Best way of having fast and accurate results, the fields used to do the relation mus be and index too in both tables.
Hope it helps.
From Chile
Adolfo
Ji,ji,ji... buena la cosa... "all you need is code"
The problem is my knowledge. It works great after create an index.
1) yes, 1 to 1.
2) No, Master 1 to Child 1 record.
3) Around 140 coluums.
4) NO INDEX
I created an index in MySqlQueryBrowsers and try to open the sample program a again. It took 1-2 seconds only.
1.- Are the tables 1 to 1.... 1 Record in ccrtbl and 1 record in ccrgst ?
2.- Are the tables master to child.... 1 Record in ccrtbl and many records in ccrgst ?
3.- How many fields are there in the 2 tables ?
4.- Both tables have an Index with the relation field ?
Answers to possible errors...
1.- Here is the most perfect relation... there must be no error and the query is very fast... if there is 1 to 1 relation
2.- Here you must thing backwards.... Select on the child table... the numbers of resulting rows must be the initial number of records in that table. So very fast indeed.... BUT. If you do it all the way around you may have a big resulting query, then a slow query result.
3.- If you have 50 fields on the tables, you must assume that you are going to get a 100 fields query. Then the idea is to reduce it to have only the required fields to work in the query. Also try to use alias.
Ex.
Select chi.code, chi.number, chi.ammount, mas.date, mas.client from child as chi left join master as mas on chi.CodeMas=mas.Code
4.- Best way of having fast and accurate results, the fields used to do the relation mus be and index too in both tables.
Hope it helps.
From Chile
Adolfo
Regards,
Dutch
FWH 19.01 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio
FWPPC 10.02 / Harbour for PPC (FTDN)
ADS V.9 / MySql / MariaDB
R&R 12 Infinity / Crystal Report XI R2
(Thailand)