Using a query's result in another

Hi

  I don't know if i can use a query's result in another
query.If it's possible,How?

Example:
Query1.sql.text:='select * from table1';
Query1.open;

Query2.sql.text:=???

Thanks
s_arbAsked:
Who is Participating?
 
FrodoBegginsConnect With a Mentor Commented:
Why don't you use

select a.code,a.qty1,b.qty2 from
  (select code,sum(qty) as qty1  from table
  where DocDate<=date1 group by code) a,
  (select code,sum(qty) as qty2  from table
  where DocDate<=date2 group by code) b
where a.code = b.code(+)
0
 
jswebyCommented:
You don't actually need to run one query and then run another one to use the results from the first one. SQL supports embedded SELECT statements, such as:

Query2.SQL.Text := 'SELECT * FROM Table2 WHERE PrimaryKey IN (SELECT Identity_field FROM Table1);

Or for a more specific result set:

Query2.SQL.Text := 'SELECT * FROM Table2 WHERE PrimaryKey = (SELECT Identity_field FROM Table1 WHERE Job_number = 1);

Obviously you replace the table and field names to ones which are relevant to you. If you need any more help, add a comment.

J.
0
 
s_arbAuthor Commented:
I give an example with FoxPro.

select code,sum(qty) as qty1  from table where DocDate<=date1 group by code into cursor qry1

select code,sum(qty) as qty2  from table where DocDate<=date2 group by code into cursor qry2

* date1>date2

select a.code,a.qty1,b.qty2 from qry1 a,qry2 b
union
(select code,qty1,0 as qty2 from qry1 where code not
in (select code from qry2))


I don't know how i can join to one SQL Statement.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
MarcGCommented:
you can also do it in delphi, not in sql to mix the results
would be like this

Query1.sql.text := 'SELECT MYID FROM MYTABLE WHERE MYAGE > 18';
Query1.open;

Query2.sql.text := 'SELECT * FROM MYTABLE2 WHERE ID2 = ' + Query1.FieldByName('MYID').asString;


this would be for the first result returned in Query1
for getting all you would have to do in in a while construct like

Query1.open;
Query1.first; // not really necessary but I normaly do it
while not Query1.EOF do
begin
  Query2.sql.text := query2.sql.text + ' OR ID2 = '+ Query1.FieldByName(MYID).AsString;
  next;
end;
Query2.open;

depending on what you need and what data you have you might need to change this a bit...
0
 
s_arbAuthor Commented:
FrodoBeqqins
  Your Code is nice,but when i use it in a query
A syntax error arises concerning after "From " clause.
0
 
FrodoBegginsCommented:
Maybe in FoxPro subqueries are not alowed. In Oracle it works.
Usualy there is a walkarround. You chould create a view with the subquery. But it doesen't work with dynamic subqueries. Unless you create id dinamicaly too.
0
 
s_arbAuthor Commented:
Excuse Me,
My Programm is written with DELPHI, and my database is access.
FoxPro Was only an example.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.