>Greetings, I'm trying to SELECT 3 tables, the first two are related by partno, the 3rd is a table that holds abbreviations and their long conterparts...
>The 2 tables with the bulk of the data are ONHND and MASTER; I use Group to translate the abbreviations in MASTER to their full meaning, thus Group is basically a 2 column table(gabbr,gname), with about 16 rows...
>This is what I've currently got:
>
>SELECT ONHND.FPARTNO, MASTER.FDESCRIPT, ONHND.TOTONHAND, MASTER.FGROUP, GROUP.GNAME;
> FROM GROUP RIGHT OUTER JOIN MASTER INNER JOIN ONHND;
> ON MASTER.GROUP=GROUP.GABBR;
> ON ONHND.PARTNO=MASTER.PARTNO;
> WHERE MASTER.GROUP=="C"
>
>
>"C" is abbreviation for "CURRENT"...this is in the Group table and works fine if I inner join the MASTER and GROUP on Group=gabbr; but when I do the above and try to include stuff from ONHND, like TOTalONHAND, then the SELECT takes FOREVER!! If I undo the Join between GROUP and MASTER, then my WHERE clause doesn't work....
>
>Thus my question is...What's the best way to get this to work correctly AND run the querry FAST? ANY help is appreciated:)
Either chenge the order of "ON..." clauses so :
ON ONHND.PARTNO=MASTER.PARTNO;
ON MASTER.GROUP=GROUP.GABBR;
Or :
FROM GROUP RIGHT OUTER JOIN MASTER ;
ON MASTER.GROUP=GROUP.GABBR;
INNER JOIN ONHND;
ON ONHND.PARTNO=MASTER.PARTNO;
Otherwise you'd get that "forever" join. Take a look at how 4 tables in testdata.dbc (home()+"samples\data") are connected in a view.
Cetin