Thanks all for the responses. I get a lot from this forum. Pardon the
verboseness of my posts, but I am excited about what I am building,
and want to share this with the Rev community when it is done. I think
this can be a very useful tool for people who need to use rev to
manage large datasets in a multiuser environment, but really do not
have the time or wherewithal to develop their own methods.
I should explain that I am not hard coding for a single application.
The command and function set I am writing is actually a framework of
code that will work for ANY database application. I am kind of
replicating what Filemaker is to databases. I am building a database
application development system. The Inventory/Help Desk app I am
writing serves as a test model of sorts, but the real value is that I
am writing my code to be very portable.
Ever find yourself saying, "Man, I wish I could build databases as
easily as Filemaker, but have the complete control and flexibility of
Revolution coding"? That is what I am up to.
So on to the issues.
A simple join would work for this as well:
SELECT * FROM employees, departments WHERE employees.deptid =
departments.deptid
As a bonus, this way you could easily pick what fields you wanted from
each table. For example:
SELECT employees.id,employees.name,departments.name FROM employees,
departments WHERE employees.deptid = departments.deptid
It looks like your query above is going the opposite direction,
looking up customers that match the deptid in departments. I am
looking for department records whose deptid only exist in the customer
cursor. More on paging later.
Aye, I could return all the data with one query. The problem though is
that my app is going to contain some fairly complex queries with
multiple relations, and I have to construct these queries via
conditional coding. Also, I do not WANT to return data from 2 tables
in one cursor. First of all, I have like data in multiple tables
(deptid in customers and also in departments for example) as well as a
signature field, a deleted flag field, and a unique ID field in EVERY
table. I need these values for every record in every table. That would
mean I would have to use column aliases, so now my SQL construction
code would have to be orders of magnitude more complicated, and much
more difficult to troubleshoot should the queries not return what I
expect. Not impossible, just complicated.
The method I am using instead is to return each table as it's own
cursor, resulting in MUCH simpler code to construct the queries, and
simpler queries themselves. Additionally, the signature, delete and
uniqueid fields can never be confused beacause each cursor for each
table retains it's own identity. So how is it relational? Well I wrote
my own relational system into my application, so that when I navigate
to a record in a cursor, I also look up the records in and child
subservient to that cursor. (IMHO that is what relational really
means). Now all I have to do is get values from the current records in
each cursor.
I also have the advantage of being able to present the entire child
cursor to the user in a one-to-many environment, as in an invoice
where there is a master record and many detail records. My table data
is ready made for my invoice detail. It's all in it's own cursor.
Actually, Bob, you don't need to do two queries... you can do it in
one...
also, you don't need to use LIMIT unless you specifically want less
records
than what would normally be returned:
SELECT * FROM departments WHERE departments.deptid IN (SELECT
employees.deptid FROM employees)
The LIMIT is necessary because I am working towards a paging system
that will handle HUGE databases of unlimited size. This will get
around Revolutions limits on how much data can be returned in a cursor
(the limits of which I still do not have a definitive answer on). This
is why I need to use the SELECT for the master table as a lookup in
the child table.
I do not use Revolution's built in queries because what I need to do
is more than just read data from a table and let the user edit it. I
need to do complex validations. For instance using the example above,
if someone edits a deptid in the department table, I need to go find
every other table that uses deptid, look up the old value and change
it to the new value. Otherwise I break the relational link between
those records.
Bob Sneidar
IT Manager
Logos Management
Calvary Chapel CM
_______________________________________________
use-revolution mailing list
[email protected]
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
