Solved

Using a query's result in another

Posted on 2001-06-24
7
221 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 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

717 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