Accessing multiple tables in SQLEXEC statement, SQL Pass Through

DizzleBeans 0
Viewed 39

I create a cursor called 'table1_data' from the statement:

lnResult1 = SQLEXEC(m.hConn, 'select * from table1 where status like ?m.seeStatus','table1_data')

Now I want to access this table in my next SQLEXEC statement:

lnResult2 = SQLEXEC(m.hConn, 'select * from table2 where table2.item_id = table1_data.item_id','table2_data')

however I get the error: The multi-part identifier table1_data.item_id could not be bound. Any help much appreciated!

foxpro visual-foxpro sql
2 Answers

You can join both tables, but instead of SELECT * you should always neme the columns that you only need.

that helps also in future.

lnResult1_2 = SQLEXEC(m.hConn, 'select table1.*,table2.* from table1, table2 where table2.item_id = table1_data.item_id AND  status like ?m.seeStatus')

I can't find any code related to joins, but

lnResult1_2 = SQLEXEC(m.hConn, 'select table1.*,table2.* from table1 JOIN table2 ON table2.item_id = table1_data.item_id WHERE  status like ?m.seeStatus')

should also work, but as i said i can't find any code that uses explicit join, so you can give it a try and give a short response

nbk0

In first case table1_data is a local cursor and you cannot use it in second call which is made to server. However since table1_data is also coming from server side you could instead do this:

*** Using text ... endtext just to make the SQL more readable

local lcSQL 
text to m.lcSQL noshow
select * from table2
where exists (select * from table1
              where table2.item_id = table1_data.item_id and 
                    table1.status like ?m.seeStatus);
endtext

lnResult1 = SQLEXEC(m.hConn, 'select * from table1 where status like ?m.seeStatus','table1_data')
lnResult2 = SQLEXEC(m.hConn, m.lcSQL,'table2_data')

EDIT: BTW you can return 2 results from a single SQLExec() call however it is not worth it here in this case. Two separate queries as above is just fine (and remember you could make them updatable per table).

Cetin Basoz0
Related