"complex" sql : how to retrieve data ?

Solar2D Lua Language
#1

Hi,

I have a correct sql string (I can retrieve the data from Terminal or SQLBrowser, but I can not in Lua).

I can count the number of rows (there are 4, and it is correct), but I can not retrieve the content.

Any idea ?

[code]

local sql = “select distinct villes.nom_ville, villes.pays FROM evenements, villes WHERE evenements.id_ville=villes.code”

for row in db_now:nrows(sql) do
print( tostring(row.villes.nom_ville) ) – this does not work
end

[/code] [import]uid: 5578 topic_id: 24800 reply_id: 324800[/import]

#2

For people with same problem, you do not have to mention the name of the table :

local sql = "select distinct villes.nom\_ville, villes.pays FROM evenements, villes WHERE evenements.id\_ville=villes.code"  
for row in db\_now:nrows(sql) do  
 print( tostring(row.nom\_ville) ) -- this works  
  
end

thanks to myself :wink: [import]uid: 5578 topic_id: 24800 reply_id: 100560[/import]

#3

BTW, it’s the dots that were throwing you off. Lua treats those as table indexes.

So asking for row.villes.nom_ville, LUA interprets the request as a lookup in a table such that:
[lua]row = {
villes = { nom_ville = “” }
}[/lua]

You might have also been able to change the select statement:

[mysql]
SELECT DISTINCT villes.nom_ville AS target …
[/mysql]

Then use row.target in the nrows loop. [import]uid: 44647 topic_id: 24800 reply_id: 100563[/import]

#4

Hi toby2,

This is a great idea as I have some row from different tables whicj have the same names and this was my next problem :wink:

Thanks a lot !!

Frédéric
[import]uid: 5578 topic_id: 24800 reply_id: 100564[/import]

#5

I have a more complex SQL and I need to mention the name of the table because I have 4! 

SELECT T2.AREA, T2.PS_POSID1, T2.NAME, T3.PS_POSID2, T3.NAME AS NAME2,T1.TCODE, TT.TTEXT FROM transactions AS T1 INNER JOIN (app01 AS T2, app02 AS T3, transacttext AS TT) ON (T2.PS_POSID1=T1.PS_POSID1 AND T3.PS_POSID2=T1.PS_POSID2 AND TT.TCODE=T1.TCODE) WHERE TT.LANGU = ‘E’ AND T2.LANGU = ‘E’ AND T3.LANGU = ‘E’ AND ( T1.FREQ = ‘A’ OR T1.FREQ = ‘B’ ) ORDER BY T2.AREA, T2.PS_POSID1, T3.PS_POSID2, T1.TCODE

What can I do?

Thanks in advance :wink:

#6

edit - ignore this post. I saw your other thread duplicating the question. Best to continue there.

#7

I have a more complex SQL and I need to mention the name of the table because I have 4! 

SELECT T2.AREA, T2.PS_POSID1, T2.NAME, T3.PS_POSID2, T3.NAME AS NAME2,T1.TCODE, TT.TTEXT FROM transactions AS T1 INNER JOIN (app01 AS T2, app02 AS T3, transacttext AS TT) ON (T2.PS_POSID1=T1.PS_POSID1 AND T3.PS_POSID2=T1.PS_POSID2 AND TT.TCODE=T1.TCODE) WHERE TT.LANGU = ‘E’ AND T2.LANGU = ‘E’ AND T3.LANGU = ‘E’ AND ( T1.FREQ = ‘A’ OR T1.FREQ = ‘B’ ) ORDER BY T2.AREA, T2.PS_POSID1, T3.PS_POSID2, T1.TCODE

What can I do?

Thanks in advance :wink:

#8

edit - ignore this post. I saw your other thread duplicating the question. Best to continue there.