Re: [sqlite] retrieving data from multiple tables

[T&B]

Hi Kirrthana,

    I have created a database with four tables,i have to search and  
retrieve
data from all the four tables based on the entry i get from the  
previous
table and display all the entries,could anybody tell how to do it.

I'm not sure what you mean. If you mean you have four tables with  
similar fields, and you want to SELECT data from all of them at once,  
then you can create a dynamic compound table that will contain all  
the data, and SELECT from that. eg:

CREATE VIEW MyTables
   AS
         SELECT * FROM MyTable1
      UNION ALL
         SELECT * FROM MyTable2
      UNION ALL
         SELECT * FROM MyTable3
      UNION ALL
         SELECT * FROM MyTable4;

SELECT * FROM MyTables WHERE <insert your criteria here>;

Or, if you mean that the result of SELECTing in one table becomes the  
basis of the SELECT in the next, that is something like the sequence:

value1 = SELECT ResultField1 FROM MyTable1 WHERE SearchField1 =  
<initialValue>;

value2 = SELECT ResultField2 FROM MyTable2 WHERE SearchField2 =  
ResultField1;

value3 = SELECT ResultField3 FROM MyTable3 WHERE SearchField3 =  
ResultField2;

value4 = SELECT ResultField4 FROM MyTable4 WHERE SearchField4 =  
ResultField3;

Then I think you could do it in one action by:

SELECT ResultField4
   FROM
         MyTable1
      LEFT JOIN
         MyTable2
            ON SearchField2 = ResultField1
      LEFT JOIN
         MyTable3
            ON SearchField3 = ResultField2
      LEFT JOIN
         MyTable4
            ON SearchField4 = ResultField3
   WHERE
      SearchField1 = <initialValue>;

Tom


-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------