Page 1 of 1
How to use JOIN command in MySql.LIB?*Understood/More Clear*
Posted: Tue Jun 23, 2015 6:20 am
by dutch
I try with JOIN command but it doesn't successful. How can I use it?
Code: Select all
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")
Re: How to use JOIN command in MySql.LIB?
Posted: Tue Jun 23, 2015 7:29 am
by AntoninoP
Hi,
have you problem with the join command in general?
It depends on what result you want and on tables structures.
Have you try the select command in a another context?
Take a look a those pages, They explain Join command very well:
http://www.w3schools.com/sql/sql_join.asp
http://www.codeproject.com/Articles/330 ... -SQL-Joins
Hope I am helping,
Regards,
Antonino Perricone
Re: How to use JOIN command in MySql.LIB?
Posted: Tue Jun 23, 2015 8:35 am
by Enrico Maria Giordano
Dutch,
dutch wrote:Code: Select all
SELECT * FROM ccrtbl LEFT JOIN ccrgst ON ccrtbl.tbl_gstno=ccrgst.gst_intno
The query syntax looks ok. What error do you get?
EMG
Re: How to use JOIN command in MySql.LIB?
Posted: Tue Jun 23, 2015 9:45 am
by dutch
Dear Antonio and EMG,
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.
Re: How to use JOIN command in MySql.LIB?
Posted: Tue Jun 23, 2015 11:57 am
by Adolfo
Dutch.. you must be more specific..
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
Re: How to use JOIN command in MySql.LIB?
Posted: Wed Jun 24, 2015 2:46 am
by dutch
Dear Adolfo,
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.
Code: Select all
CREATE INDEX ccrgst1 ON ccrgst (gst_intno)
Thank you so much for all.
Adolfo wrote:Dutch.. you must be more specific..
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
Re: How to use JOIN command in MySql.LIB?*Understood/More Clear*
Posted: Wed Jun 24, 2015 1:20 pm
by Adolfo
Great it helps...
Any other doubt do not hesitate... and contact me here... or to my private email.
Greetings
From Chile.
Adolfo
Re: How to use JOIN command in MySql.LIB?*Understood/More Clear*
Posted: Wed Jun 24, 2015 11:08 pm
by dutch
Thank you so much for your kindness.
Adolfo wrote:Great it helps...
Any other doubt do not hesitate... and contact me here... or to my private email.
Greetings
From Chile.
Adolfo