Re: [sqlite] retrieving data from multiple tables

[T]

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

[Kirrthana M]

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

[T]

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

[T]

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

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

[T]

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

Re: [sqlite] Retrieving data

[Dennis Cote]

Richard Stern wrote:

Hi all.

I'm using sqlite in VC++ 2005.
When I started this I knew nothing about sqlite or indeed SQL at all so its
been tough going trying to work out how this all works.

So far I have created a database and a table and added columns and rows
filled with data. But I'm having trouble retrieving that data.

Lets say I have the columns MemberNo, Name and Address.
I want to use a specific MemberNo to retrieve a name and address and store
them in separate variables.

I tried:
sqlite3_exec(AccDataBase,"SELECT Name,Address FROM Accounts WHERE MemberNo =
2;",Callback(cError,10,,),test,);

Now I don't fully understand how the callback part works so I just made the
variables that seemed appropriate and threw them in. I thought the "result"
one was supposed to get filled by the result of the SELECT, but it wasn't.

When I ran this, no error was returned but the callback didn't seem to do
anything.

So is this the correct command to use? Is there a better/easier way?

  

Rick,

To use the callback interface you need to define a callback function in 
your C code. This function MUST have the correct signature so it can be 
called correctly by sqlite. This signature is defined in sqlite3.h as


/*
** The type for a callback function.
*/
typedef int (*sqlite3_callback)(void*,int,char**, char**);

The sample program at http://www.sqlite.org/quickstart.html shows how to 
define a callback function.


static int callback(void *user_data, int argc, char **argv, char **azColName)
{
'''
}

Now, when you call sqlite3_exec() you need to pass the address of this 
function (i.e. its name) to sqlite so that it can call your function 
when it has a result. Your call will look something like the one on the 
quickstart page.


rc = *sqlite3_exec*(db, sql_query, callback, my_data, );


Each time sqlite has a result from your query it will call your callback 
function and pass it the result data and the column names as parameters. 
Your function can then do whatever it wants to with this data before it 
returns. If your query generates 100 result rows, your callback function 
will be called 100 times.


After you get the sqlite3_exec version working, you should look at the 
sqlite3_prepare, sqlite3_step, sqlite3_column_* set of APIs which are 
newer and generally easier to use for queries.


HTH
Dennis Cote

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

Re: RE: [sqlite] Retrieving data

[thomas . l]

Hi Richard

>> You don't need a Callback-Function in any case. Try it
>> without

> I'm confused.
> How does the SELECT command return any data? In what
> form would it give you this data back? There doesn't seem to
> be a pointer to pass by reference and no out variables.

The Prepare-Command with it's given SQL-Statement doesn't
return Data. *Imho* it prepares only the Database-Engine. I
fetch the Data after I "prepared" the Database with
"_sqlite3_step()".

I say it again. Spend a little time to my samples.

Best Regards
Thomas

--
www.thlu.de



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

RE: [sqlite] Retrieving data

[Richard Stern]

 
> > I tried:
> > sqlite3_exec(AccDataBase,"SELECT Name,Address FROM Accounts WHERE
> MemberNo =
> > 2;",Callback(cError,10,,),test,);
> 
> You don't need a Callback-Function in any case. Try it
> without

I'm confused.
How does the SELECT command return any data? In what form would it give you
this data back? There doesn't seem to be a pointer to pass by reference and
no out variables.

Rick



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

Re: [sqlite] Retrieving data

[thomas . l]

Am 14.09.2006 um 02:11 Uhr haben Sie geschrieben:

> I'm using sqlite in VC++ 2005.
> When I started this I knew nothing about sqlite or indeed SQL at all
so its
> been tough going trying to work out how this all works.

I started so too ;-)

> I tried:
> sqlite3_exec(AccDataBase,"SELECT Name,Address FROM Accounts WHERE
MemberNo =
> 2;",Callback(cError,10,,),test,);

You don't need a Callback-Function in any case. Try it
without

I performed two little Samples on my HP. Take a look.

> Now I don't fully understand how the callback part works so I just
made the

It's coming soon, because you work with it

Best regards, Thomas

--
www.thlu.de



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