Using a query's result in another


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

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


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(+)
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.

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
(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.
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.

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';;

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.first; // not really necessary but I normaly do it
while not Query1.EOF do
  Query2.sql.text := query2.sql.text + ' OR ID2 = '+ Query1.FieldByName(MYID).AsString;

depending on what you need and what data you have you might need to change this a bit...
s_arbAuthor Commented:
  Your Code is nice,but when i use it in a query
A syntax error arises concerning after "From " clause.
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.
s_arbAuthor Commented:
Excuse Me,
My Programm is written with DELPHI, and my database is access.
FoxPro Was only an example.
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.