How to use JOIN command in MySql.LIB?*Understood/More Clear*

[dutch]

I try with JOIN command but it doesn't successful. How can I use it?

Code: Select all  Expand view

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")

[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/33052/Visual-Representation-of-SQL-Joins
Hope I am helping,
Regards,
Antonino Perricone

[Enrico Maria Giordano]

Dutch,

Code: Select all  Expand view

SELECT * FROM ccrtbl LEFT JOIN ccrgst ON ccrtbl.tbl_gstno=ccrgst.gst_intno

The query syntax looks ok. What error do you get?

EMG

[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.

[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

[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  Expand view

CREATE INDEX ccrgst1 ON ccrgst (gst_intno)

Thank you so much for all.

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

[Adolfo]

Great it helps...

Any other doubt do not hesitate... and contact me here... or to my private email.

Greetings
From Chile.
Adolfo

[dutch]

Thank you so much for your kindness.

Great it helps...

Any other doubt do not hesitate... and contact me here... or to my private email.

Greetings
From Chile.
Adolfo