Database Doldrums

[Frank D. Engel, Jr.]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

There is no specific Rev command to do a Join, that is a function of 
your database server, and you only use a single database ID.

Are you using the database library calls directly, rather than the 
"Database" properties things (which I could never get working correctly 
as I'd at one time hoped to)?

If so, you need to put the JOIN SQL command line into the 
revQueryDatabase function call...


For example, let's say you have three tables as follows (PostgreSQL 
example):

CREATE TABLE students
(
     studentID   serial primary key,
     name          text,
     address      text, ...
);

CREATE TABLE classes
(
     classID       serial primary key,
     name          text,
     credits         integer
);

CREATE TABLE registration
(
     studentID    integer references students,
     classID        integer references classes
);

Now maybe you want a list of all students currently enrolled in 
1-credit classes.  You could do this with an SQL statement like this:

SELECT DISTINCT students.name
FROM students, classes, registration
WHERE (students.studentID = registration.studentID) AND 
(classes.classID = registration.classID) AND (classes.credits = 1)

Note: this is only one of many ways to write an SQL statement to do 
this, and this is just a quick example off the top of my head so there 
may be mistakes in the above, but it should give you the basic idea.

Now in Rev, you'd execute that query with:

put revQueryDatabase(databaseID, "SELECT DISTINCT students.name FROM 
students.......credits = 1)") into queryID

Then use revMoveToNextRecord, revCloseCursor, 
revDatabaseColumnNumbered, etc. to get the info you want and then clean 
up.


On Dec 29, 2004, at 6:20 AM, John Miller wrote:

> Greeetings All,
>
> I have been working with our company's databases using ODBC and have 
> been amazed at how powerful and FAST Revolution is.
>
> Yesterday, I started a new project where I need to JOIN information 
> from 2 different tables, and I am stymied.
>
> I understand how to write the SQL command line, but I can't figure out 
> which rev command I need to use. All of them seem to accept only one 
> database ID.
>
> Can someone point me in the right direction.
>
> Thanks for all your help.
>
> John Miller
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> http://lists.runrev.com/mailman/listinfo/use-revolution
>
>
- -----------------------------------------------------------
Frank D. Engel, Jr.  <fde101 at fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten 
Son, that whosoever believeth in him should not perish, but have 
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB0rzl7aqtWrR9cZoRAraxAJ9uFsaMzy62PlMoEgUBm7tT76fFPQCfWdVm
AclFgyB19KcuBxIHP9h2RQc=
=zL2u
-----END PGP SIGNATURE-----



___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com