Read from 2 SQLite tables...

[FrancescoDiMuro]

Hi guys! How are you? Hope you're fine
I'm trying to use SQLite for managing some data, and, I would like to display my "retrieved" data, but I'm trying to do a select from 2 table, and seems to not work properly...
What I'd like to do is retrieve data from 2 tables and display in a listview...

I tried with:

Local $aRisultato, $iRighe, $iColonne, $iRVal
    $iRval = _SQLite_GetTable2d($hDatabase, "SELECT * FROM Magazzino_Rockwell, Magazzino_Siemens;", $aRisultato, $iRighe, $iColonne)
    If $iRVal = $SQLITE_OK Then
    ;_SQLite_Display2DResult($aRisultato)
    _ArrayDisplay($aRisultato)
    EndIf

... but it displays a single record 41 times, and it does this thing for every record in the database...
What do you need to help me?
Thanks  

EDIT:
Managed with a double If and double query like this:
 

$iRval = _SQLite_GetTable2d($hDatabase, "SELECT * FROM Magazzino_Rockwell;", $aRisultato, $iRighe, $iColonne)
    If $iRVal = $SQLITE_OK Then
        Local $aRisultatoRockwell = $aRisultato
        $iRval = _SQLite_GetTable2d($hDatabase, "SELECT * FROM Magazzino_Siemens;", $aRisultato, $iRighe, $iColonne)
        If $iRVal = $SQLITE_OK Then
            _ArrayConcatenate($aRisultato, $aRisultatoRockwell)
            Local $i, $sRiga, $s_LV_Item
            For $i = 1 To Ubound($aRisultato) - 1
                $sRiga = $aRisultato[$i][0] & "|" & _
                         $aRisultato[$i][1] & "|" & _
                         $aRisultato[$i][2] & "|" & _
                         $aRisultato[$i][3] & "|" & _
                         $aRisultato[$i][4] & "|" & _
                         $aRisultato[$i][5] & "|" & _
                         $aRisultato[$i][6] & "|" & _
                         $aRisultato[$i][7] & "|" & _
                         $aRisultato[$i][8] & "|" & _
                         $aRisultato[$i][9] & "|" & _
                         $aRisultato[$i][10] & "|" & _
                         $aRisultato[$i][11]
                $s_LV_Item = GUICtrlCreateListViewItem($sRiga, $lv_Lista)
            Next

And so, the listview is created
If anyone has another more efficient way, I'm here Thanks guys  
 

Edited January 25, 2017 by FrancescoDiMuro

[FrancescoDiMuro]

Hey guys!
Can someone please explain me how can I retrieve only 1 field from a table?
I.E.: I would like to retrieve the amount of a certain product... How could I set the query, in order to retrieve only the amount of that product, as a single string, and not an array... How can I do it?  
Thanks  

[jchd]

Answer to 1rst post: what is the relationship between the 2 tables? You're asking for the cross product of the two tables (= sets) and that is exactly what the SQL engine yields. Maybe you want to list all rows from both tables. If so, use:

select * from table1
union all
select * from table2

Note: I suspect that if both tables have the same columns and semantics, they should be merged and a column added to discriminate between Rockwell or Siemens.

2nd post: select MYCOLUMN from ...

Note that select * from ... is 99.99% of the time a very bad idea.

[FrancescoDiMuro]

Good morning @jchd
For the first question, I answer Yes.
I want to see all the data contained in the two tables...
For the second post... What should I use? I managed with, but If you have any other idea, please tell me.

_SQLite_QuerySingleRow()

 Thanks  

 

[jchd]

It's OK as far as the row you want to get is acually the first one in the resultset returned by your query.

[FrancescoDiMuro]

Yeah, because the result, in case of "Product found", it's always composed from 2 fields...  
Thanks for your time and have a good day!