Another SQL question

[Bob Sneidar]

Ken this is exactly what I am looking for. I was parsing the lookup  
key for departments from the employee cursor into a text string, and  
then using that as a lookup for my department records, so that I ended  
up with a cursor of department records that had matching values in the  
employee cursor. That way I can get the department description and the  
manager etc. right from the department table without replicating data.

The idea is to be able to page through large data sets by setting a  
limit on the number of records I return in the parent cursor, and then  
only return records in the "relational" cursors whose keys match the  
parent. I don't want to use the SQL relational system, because frankly  
writing rev code to build highly complex queries to return data from  
multiple tables into one cursor on the fly is more trouble than I want  
to deal with. I wish there was a way in SQL to set up linked cursors  
for relations, but alas it is what it is.

So I am building into my application a more "traditional" approach to  
relations, where I return separate cursors for every child table, and  
then have a navigation function that looks up the records in the child  
tables each time I navigate to a new record in the parent table. It  
may seem a more complex way to do things, but the advantage is the  
queries for each table are quite simple, and the mechanics for what  
data gets returned are mostly under Rev code and not a long and highly  
complex SQL statement. Think of what this would mean for an invoicing  
app that would only return invoice detail for the current invoice in a  
separate table that could be presented to the user as a list.

So using your method, my queries for a table of 25 employees with the  
associated departments data would look something like this:

  -- employee data
put "SELECT employees.deptid FROM employees LIMIT 25" into  
mEmployeeQuery

-- department data
put "SELECT * FROM departments WHERE departments.deptid IN (" &  
mEmployeeQuery & ")" into mDepartmentQuery

-- color added just because I can :-)

In this example I use as my subselection the exact same query I used  
for the parent table. That would ensure that the children ALWAYS  
contained the records in the parent cursor no matter how I paged my  
data.

When I am done with this, I will have more than just an employee  
database or an inventory system. I will have a Revolution database app  
builder. Every card represents a table in the SQL database you connect  
to. I have a "Field Dropper" stack that will read the SQL database and  
present the user with columns for each table. It will then create a  
field on a card for any selected columns with everything it needs to  
work with the database app. If you drop a field from a table other  
than the one for the card you are working with, it allows you to  
select a column from the parent. It then sets up the relation in a  
custom card property, and configures the field as a related table field.

Whenever I enter a card or navigate to another record I call a  
function that reads the relations and queries (or requeries as  
necessary) all the tables in the relations property for the card. I  
have record and table locking and update checks already built in so I  
never overwrite another user's updates if they got to the record  
first. I have it reading and writing to the database now. I just need  
to add search functions and field validation functions and away I go.

Thanks a bunch to everyone for their help. I will post a version as  
soon as it becomes functional enough to use without any major bugs.

Bob Sneidar
IT Manager
Logos Management
Calvary Chapel CM

On Feb 4, 2009, at 9:02 AM, Ken Ray wrote:

> The other thing to note is that "IN" can be used both for checking for
> matching value in field(s) in another table with a "subselect", or  
> even for
> a simple string comparison.
>
> For example, you can do this (this is admittedly a bad example  
> because you
> don't need to do the subselect, but it's more FYI):
>
>  SELECT * FROM People WHERE PhoneLink IN
>       (SELECT RecID FROM Phones WHERE PhoneType='Fax')
>
> And you can also do this:
>
>  SELECT * FROM Phones WHERE PhoneType IN ('Fax','Work','Home')
>
> etc.
>
> The reason I mention this, is that when I first went to look for  
> this a
> while ago, I thought it would be under SELECT, but now I know better.
>
> :-)
>
> Ken Ray
> Sons of Thunder Software, Inc.
> Email: kray at sonsothunder.com
> Web Site: http://www.sonsothunder.com/