Solved

Using a query's result in another

Posted on 2001-06-24
7
213 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Accepted Solution

by:
FrodoBeggins earned 50 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
IExtractImage Delphi 14 168
How to use Hashing sha1 in Delphi 2010 4 163
creating threads in delphi 1 55
Create a path if not exists 7 47
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…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now