RE: [sqlite] retrieving data from multiple tables

[Kirrthana M]

I have four tables like Mytable1,Mytable2,Mytable3,Mytable4
Mytable1 and Mytable2 have one similar field rest al different,
Mytable2 and Mytable3 have one similar field rest al different,
Mytable3 and Mytable4 have one similar field rest al different,

i have to select from four tables by matching with all these field.


-----Original Message-----
From: T&B [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 14, 2007 12:44 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] retrieving data from multiple tables


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]
----------------------------------------------------------------------------
-


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