?
Solved

Using a query's result in another

Posted on 2001-06-24
7
Medium Priority
?
222 Views
Last Modified: 2010-04-06
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
0
Comment
Question by:s_arb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 4

Expert Comment

by:jsweby
ID: 6222811
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
 

Author Comment

by:s_arb
ID: 6224051
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
 
LVL 3

Expert Comment

by:MarcG
ID: 6224097
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Accepted Solution

by:
FrodoBeggins earned 150 total points
ID: 6224439
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
 

Author Comment

by:s_arb
ID: 6230241
FrodoBeqqins
  Your Code is nice,but when i use it in a query
A syntax error arises concerning after "From " clause.
0
 
LVL 2

Expert Comment

by:FrodoBeggins
ID: 6232606
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
 

Author Comment

by:s_arb
ID: 6236943
Excuse Me,
My Programm is written with DELPHI, and my database is access.
FoxPro Was only an example.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question