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. 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... i have to select from four tables by matching with all these field. By getting the search string from previous table im not only retrieving data from the table4,at each step i have to retrieve data from 3 tables and store it in the data structure. Your two last statements still seem contradictory to me, as to what you want to achieve. As per my first response, I don't understand whether you want to search one corresponding field across four databases, or search one at a time, using the result as the criteria for the next one. Please give us an example. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] retrieving data from multiple tables
By getting the search string from previous table im not only retrieving data from the table4,at each step i have to retrieve data from 3 tables and store it in the data structure. -Original Message- From: T&B [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 14, 2007 3:35 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] retrieving data from multiple tables Oops, I meant: CREATE VIEW MyTables AS SELECT Field1 AS MyField, otherFields1 FROM MyTable1 UNION ALL SELECT Field2, otherFields2 FROM MyTable2 UNION ALL SELECT Field3, otherFields3 FROM MyTable3 UNION ALL SELECT Field4, otherFields4 FROM MyTable4; > and thereafter simply query it as if it is one big table: > > SELECT * FROM MyTables WHERE MyField is desiredValue; - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] retrieving data from multiple tables
Oops, I meant: CREATE VIEW MyTables AS SELECT Field1 AS MyField, otherFields1 FROM MyTable1 UNION ALL SELECT Field2, otherFields2 FROM MyTable2 UNION ALL SELECT Field3, otherFields3 FROM MyTable3 UNION ALL SELECT Field4, otherFields4 FROM MyTable4; and thereafter simply query it as if it is one big table: SELECT * FROM MyTables WHERE MyField is desiredValue; - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] retrieving data from multiple tables
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. OK, let's say that the "similar field" in MyTable1 is Field1, and the "similar field" in MyTable2 is Field2 etc, then I think you want to create a view: CREATE VIEW MyTables AS SELECT Field1, otherFields1 AS MyField FROM MyTable1 UNION ALL SELECT Field2, otherFields2 FROM MyTable2 UNION ALL SELECT Field3, otherFields3 FROM MyTable3 UNION ALL SELECT Field4, otherFields4 FROM MyTable4; and thereafter simply query it as if it is one big table: SELECT * FROM MyTables WHERE MyField is desiredValue; I put in "otherFields" since I'm assuming that you want to return some fields other than the one you're searching. Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] retrieving data from multiple tables
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 ; 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 = ; 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 = ; Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
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 ; 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 = ; 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 = ; Tom - To unsubscribe, send email to [EMAIL PROTECTED] -