[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using a query's result in another

Posted on 2001-06-24
7
Medium Priority
?
224 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
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month18 days, 21 hours left to enroll

834 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