Another SQL question

[Bob Sneidar]

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