two tables into one cursor

Dear sirs
Is there any way enables me to put the output of two SELECT statements into
one cursor
ex:
select sid, sname from students into Cursor sCurs
select did, dname from departments into Cursor sCurs

so sCurs has the values of the two tables
can I do that?? and how??

thanks
Mohammed

Andrew
Thu Dec 02 06:09:08 CST 2004

Mohammed Abdel-Razzak wrote:
> Dear sirs
> Is there any way enables me to put the output of two SELECT
> statements into one cursor
> ex:
> select sid, sname from students into Cursor sCurs
> select did, dname from departments into Cursor sCurs
>
> so sCurs has the values of the two tables
> can I do that?? and how??


UNION:

SELECT sid, sname FROM students ;
UNION ALL ;
SELECT did, dname FROM departments INTO CURSOR sCurs

Using UNION ALL, identical entries are preserved. If you use just UNION,
identical records are suppressed. You can see the help for it halfway
through the help entry for "SELECT - SQL".

It can be ... onerous to say the least, the output of the combined queries
must have *identical* field structures and Foxpro is very good at creating
illegal structures when you start selecting calculated fields. In this case,
I create a cursor and fill it with the results of the queries.

--
HTH
Andrew Howell

Sietse
Thu Dec 02 06:14:50 CST 2004

Hi Mohammed,
Use the UNION statement:
select sid, sname from students into;
UNION ;
select did, dname from departments into Cursor sCurs

I don't know if you need to know which is which in the resultset, but you
can do that by adding a custom type field:
select sid, sname, "S" AS Type from students into;
UNION ;
select did, dname, "D" AS Type from departments into Cursor sCurs

HTH,
Sietse Wijnker

"Mohammed Abdel-Razzak" <MohammedAbdelRazzak@discussions.microsoft.com>
wrote in message news:DCA24741-CA6B-4ACA-8BD9-685B0331A176@microsoft.com...
> Dear sirs
> Is there any way enables me to put the output of two SELECT statements
> into
> one cursor
> ex:
> select sid, sname from students into Cursor sCurs
> select did, dname from departments into Cursor sCurs
>
> so sCurs has the values of the two tables
> can I do that?? and how??
>
> thanks
> Mohammed
>

Anders
Thu Dec 02 06:35:15 CST 2004

Hello Mohammed
In order to combine the Departments and the Students tables you have to
find an table and a column that links them.
Suppose there is an Enrollment table with the sid from Students and the cid
from Courses and the Courses has a did column that identifies the Department
that gives the course. You'll then be able to make a query that shows a list
of deparments that a student is related to.

Select D.dname AS department, S.sid,S.sname AS student;
FROM Students AS S ;
JOIN Enrollment AS E ON S.sid=E.sid ;
JOIN Courses AS C ON E.cid=C.cid ;
JOIN Departments AS D ON C.did=D.did ;
ORDER BY dname, sname ;
INTO CURSOR Query101

-Anders


"Mohammed Abdel-Razzak" <MohammedAbdelRazzak@discussions.microsoft.com>
wrote in message news:DCA24741-CA6B-4ACA-8BD9-685B0331A176@microsoft.com...
> Dear sirs
> Is there any way enables me to put the output of two SELECT statements
into
> one cursor
> ex:
> select sid, sname from students into Cursor sCurs
> select did, dname from departments into Cursor sCurs
>
> so sCurs has the values of the two tables
> can I do that?? and how??
>
> thanks
> Mohammed
>