SQL library problem on JOINed tables column retrieval

[bartv]

Hello to you all,

It seems to me there are some limitations or (for me at least) unexpected behaviours to the Cursor.GetString method (and its siblings) when getting data from a SELECT stmt with a JOIN and identically named columns.
When I write my SQL as follows (and thats how I like to write them)

SELECT M.id, M.descr, T.descr
FROM tableM AS M
LEFT JOIN tableT AS T ON M.tid = T.id

getting field data with cursor.Getstring("M.descr") gives a null value.
Also not aliasing the tables does not work : cursor.Getstring("tableM.descr")

It took me some nights to discover that one solution is to not only alias the tables, but the columns also, like this

SELECT M.id AS Mid, M.descr AS Mdescr, T.descr AS Tdescr
FROM tableM AS M
LEFT JOIN tableT AS T ON M.tid = T.id

cursor.Getstring("Mdescr") is ok now. Looks like the cursor.Getstring functions don't behave correctly when a dot is involved.

Perhaps this info is of use to anyone, I did not find any information concerning this issue.
Can anyone with closer knowledge on the SQL library acknowledge this issue and also if we can expect this to be solved, or why it can't be.

Many greetings,
Bart

 

[Erel]

What value do you get for Cursor.GetColumnName(0) in the first query you posted?

 

[bartv]

Hi Erel,

Thanks for your swift response.

Even though I put M.id, M.descr, M.descr, cursor.GetColumname() returns "id" and "descr". This must be causing the problem. There is no error generated, however, when I do cursor.Getstring("M.descr"). It just returns a null value.

Cheers,
Bart

 

[Erel]

This is how the native cursor works. You will need to give an alias to these columns (as you did).

 

[metrick]

Can any body help?
I am keeping getting error message "No such column: g.k_id while compiling:
Cursor = SQ.ExecQuery("SELECT f.Shrt_Desc AS shrt, f.Eng_K AS eng FROM Table1 As f JOIN Table2 AS g ON f.k_id = g.k_id")
Table2 does contain k_id field.
Thank in advance

 

[Erel]

Try this query:

"SELECT Table1.Shrt_Desc AS shrt, Table1.ENG_K AS eng FROM Table1, Table2 WHERE Table1.k_id = Table2.k_id"

 

[metrick]

Thanks Erel:
I still get the same error message.
I have no problem select all records * from each of the tables.
Any body have working sqlite with join tables selection sample please post sample.
I have tried several sqlite join tutorial and still not working.
I have also rebuilded the sql database and tables from scratch.
Thanks in advance.

 

[Erel]

Are you sure that both tables have this field (k_id)?

 

[Mahares]

Try this:

Cursor=SQ.ExecQuery("SELECT f.Shrt_Desc AS shrt, f.Eng_K AS eng FROM Table1 f INNER JOIN Table2 g ON f.k_id = g.k_id")

or this:

Cursor=SQ.ExecQuery("SELECT f.Shrt_Desc , f.Eng_K FROM Table1 f INNER JOIN Table2 g ON f.k_id = g.k_id")

 

[metrick]

Are you sure that both tables have this field (k_id)?

Yes both tables have k_id fields.
I can Execute SQL and retrieve data in SQLite Database Browser with following with no error
SELECT f.Shrt_Desc AS shrt, f.Eng_K AS eng FROM Table1 f INNER JOIN Table2 g ON f.k_id = g.k_id"

The above SELECT statement then copy and paste in the code to retrieve the same database and tables.
Still getting the error "no such column: g.k_id,...

 

[Mahares]

The SQL statements that we supplied you are correct. I think your best bet is to post you entire project, Export as zip and include a portion of your database data in the database. I am sure someone in the forum will help , as it appears that what you are trying to do is not terribly complicated. It is a very common thing in SQLite.

 

[rboeck]

Because i found this today in the help file of SQLite Expert Prof.:

Invalid field names in views

If you create a view from multiple tables using quoted identifiers and do not use field aliases, the generated view has invalid field names. This is a known issue in SQLite, not in SQLite Expert. For more information please consult the SQLite bug list:

SQLite: Bug Reports

To work around this issue, use field aliases when generating a view. The integrated Query Builder automatically generates unique field aliases so the views created using the Query Builder are not affected by this problem.

Maybe it help.
Greetings
Reinhard

 

[Wien.Mart]

Hello All,
I am currently stomped. I wrote a right join and used it in mysql .... works fine but when used in sqlite I get an error saying right and full outer join are not supported. Argh ... is there any way around it? Please help ... thanks in advance.

 

[Erel]

Yes, SQLite doesn't support this feature: http://www.sqlite.org/omitted.html

 

[Mahares]

I have seen this somewhere a while back:
Full Outer Join
A full outer join returns all the records from the tables being joined and matches them where it can based on the specified column(s).
There is currently no provision for the use of FULL OUTER JOIN in SQLite, however you can achieve the same functionality by using a UNION clause to tie together two LEFT OUTER JOIN queries that mirror each other.
Try this:

txt="SELECT * FROM table1 LEFT OUTER JOIN table2 ON id1 = id2 UNION SELECT * FROM table2 LEFT OUTER JOIN table1 ON id1 = id2"
Cursor1=SQL1.ExcecQuery(txt)

 

[Wien.Mart]

Thanks Erel.

Thanks Mahares. The outer join works with union as shown in the example above. I'll figure out how to add a count somehow ...

 

[Mahares]

Try this. I have not had a chance to test it, but it should do it for you:

Dim RecCount As Int
txt="SELECT count(*) FROM (SELECT * FROM table1 LEFT OUTER JOIN table2 ON id1 = id2 UNION SELECT * FROM table2 LEFT OUTER JOIN table1 ON id1 = id2)"
RecCount=SQL1.ExecQuerySingleResult(txt)
Msgbox("The number of records is: " & RecCount,"")

 

[Wien.Mart]

thanks again.