SQL with large table and several other tables 1

[JDurbin]

I am building a final result set from a large table and from possibly child tables too. Here's what I got:

Master.dbf 165,000 records 85 MB
Residential.dbf
Commercial.dbf
Improvements.dbf
Child5
Child6

The master.dbf's key field is a character field for parcelnum forming a 1-Many to the children listed below it. The children are not related to each other except by having the same parent (sounds like my family )

I _thought_ this is what was meant in Hackers Guide on one of their examples of INNER JOINS. I forget what type relation they call children unrelated to each other and the book is not here. After inspection of my final SQL with SQL Showplan SYS(3054,11) I was building a Cartesian. I figured I could do something like this:

SELECT Master.* ;
FROM master, residential, commercial, improvement
INNER JOIN residential ON master.parcelnum = residential.parchel ;
WHERE INLIST(residential.field1,"1","2","3&quot ;
INNER JOIN commercial ON master.parcelnum = commercial.parcelnum ;
WHERE BETWEEN(commercial.field1,{01/01/1920},{09/15/2000}) ;
* etc * - snip -
INTO CURSOR cMaster

I am catching what tables and fields are chosen and the values to search on them fine into an array I ASCAN to get the tablename and its final filter expression to use built up from a loop.

I liked the idea of one SQL so SET TALK ON would give a progress bar but as I said ended up building a cartesian.

In a worse case scenario where all 6 tables are used in the search fields I am now using a CASE statemnet doing just 2 at a time. Joining the 2nd and 3rd tables used into a cursor cMaster. cMaster cursor with 4th table. cMaster cursor with 5th. And finally cMaster with Master table. Since the children may have more than 1 record with the same parcelnum, I use DISTINCT or GROUP BY parcelnum. I think Hackers said one is faster than the other. (I'm posting this from home.)

What I get and need is a final filtered Master table with parcelnums matching the request of the search to browse in a grid on top of a form with each child table (whether used as part of the search or not) in individual pages of a frame below the master table. (Hey that's what the customer wants)

Come to think of it I may have forgotten the DISTINCT in the single SQL above but anyways my question is What is the fastest way to do what I need? Can I do this in one SQL statement or must it be broken up step by step. In a worse case scenario such as above it takes up to 45 seconds or more on a PII 366 with 128 mb RAM on Win2K Prof. Some clients will no doubt have a mcuh slower machine. Does anyone know of a good book on sample SQL statements.
Thanks
[sig]<p>John Durbin<br><a href=mailto: john@johndurbin.com> john@johndurbin.com</a><br><a href=

http://www.johndurbin.com>

</a><br>ICQ #9466492<br>
ICQ VFP ActiveList #73897253[/sig]

 

[TomasDill]

He-he, SQL is much more complex thing that may cover any book. This require general approach, not just samples.
There are many samples of 'WHERE' conditions in the VFP help for SELECT - SQL command.

Folowing is query for you sample, I used only 3 tables from list and not sure about improvement.parcelnum field (I don't know real name . I also optimized 'WHERE' conditions to be RUSHMORable. Don't be afraind about INNER JOINS in '()'. VFP allows this and this style of joins is the most quick and optimizable for VFP. We call it 'Join to Result' joins style. Such joins may be mixed with ANSI-style joins at free. I know such details after making QUERY Builder VFP application.

SELECT Master.* ;
FROM ((master
  INNER JOIN residential ON master.parcelnum = residential.parchel) ;
  INNER JOIN commercial ON master.parcelnum = commercial.parcelnum) ;
  INNER JOIN improvement ON master.parcelnum = improvement.parcelnum ;
WHERE residential.field1 IN ("1","2","3") ;
  AND commercial.field1 BETWEEN {01/01/1920} AND {09/15/2000} ;
INTO CURSOR cMaster NOFILTER

[sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]