Foxpro 7 SELECT with JOINS

anderdw2
anderdw2 used Ask the Experts™
on
We have a legacy application that uses an advantage database server, serving .dbf   We access the data with Foxpro 7.   I'm trying to do a query like the following, but cannot get the syntax right for the JOIN   here is my sql without the join

Close Data
SET EXCL OFF

SELECT   s.batch, s.code, s.class, s.nir, s.account, s.arrival, s.return, f.name, s.describe, s.due, b.item as Special_Item, b.amount as Special_Amt, ;
             s.farmname, c.first, c.last, c.business, c.discount, c.pricetier, s.postage, c.billto, c.address1, c.city, c.state, c.zip, ;
             c.fax, c.phone, c.email;  
  FROM samples s, samplexp x, clients c, feedcode f, billitem b;
  WHERE  s.batch = 11748 AND x.location="LANCSAMPLE" AND;
          (s.sampleid = x.sampleid) AND;
              (s.account = c.account) AND;
              (s.feed_type = f.feed_type) AND;
              (s.account = b.account)
                                                   
COPY TO C:\lancasterTest\lancBill TYPE DELIMITED

Return      

I'm trying to do an LEFT/RIGHT OUTER JOIN on the following, as I don't want the data from billitem all to come over.   (s.account = b.account)

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
My English is not good so I don't understand the sentence "I don't want the data from billitem all to come over" but to understand JOINs is easy...

Your original SQL command converted to "JOIN syntax" is following:

SELECT   s.batch, s.code, s.class, s.nir, s.account, s.arrival, s.return, f.name, s.describe, s.due, b.item as Special_Item, b.amount as Special_Amt, ;
             s.farmname, c.first, c.last, c.business, c.discount, c.pricetier, s.postage, c.billto, c.address1, c.city, c.state, c.zip, ;
             c.fax, c.phone, c.email;  
   FROM samples s ;
  INNER JOIN samplexp x ON s.sampleid = x.sampleid ;
  INNER JOIN clients c ON s.account = c.account ;
  INNER JOIN feedcode f ON s.feed_type = f.feed_type ;
  INNER JOIN billitem b ON s.account = b.account ;
  WHERE s.batch = 11748 ;
    AND x.location="LANCSAMPLE"


A reason to use LEFT JOIN could be missing records with appropriate account values in billitem table, so the following command:

SELECT   s.batch, s.code, s.class, s.nir, s.account, s.arrival, s.return, f.name, s.describe, s.due, b.item as Special_Item, b.amount as Special_Amt, ;
             s.farmname, c.first, c.last, c.business, c.discount, c.pricetier, s.postage, c.billto, c.address1, c.city, c.state, c.zip, ;
             c.fax, c.phone, c.email;  
   FROM samples s ;
  INNER JOIN samplexp x ON s.sampleid = x.sampleid ;
  INNER JOIN clients c ON s.account = c.account ;
  INNER JOIN feedcode f ON s.feed_type = f.feed_type ;
  LEFT JOIN billitem b ON s.account = b.account ;
  WHERE s.batch = 11748 ;
    AND x.location="LANCSAMPLE"

will display NULL values in the Special_Item and Special_Amt columns if the record in billitem table is missing.

If you would use RIGHT JOIN then the result would contain all records from billitem table and missing values from other tables would contain NULL values.

If you need to remove all values from billitem then remove all "references" to this table from the command.
To create the command without billitem data should be easy but if it was the question, here is the result:

SELECT   s.batch, s.code, s.class, s.nir, s.account, s.arrival, s.return, f.name, s.describe, s.due, ;
             s.farmname, c.first, c.last, c.business, c.discount, c.pricetier, s.postage, c.billto, c.address1, c.city, c.state, c.zip, ;
             c.fax, c.phone, c.email;  
  FROM samples s, samplexp x, clients c, feedcode f ;
  WHERE  s.batch = 11748 AND x.location="LANCSAMPLE" AND;
          (s.sampleid = x.sampleid) AND;
              (s.account = c.account) AND;
              (s.feed_type = f.feed_type)
Olaf DoschkeSoftware Developer

Commented:
An outer join does in fact join more, the construct you have with the where clause acts like an inner join, only joining what exists in both tables. So if that's what you want, then stay with it.

You don't seem to understand what a join does, so let me explain in detail:

"lefttable left outer join righttable on joincondition" will give you all data of the left table and join data from the righttable, if the join condition is met, if there is no record in righttable fulfilling the joincondition the fields in the result which would be populated from the righttable will instead be set as null (even if field in righttable are not nullable).

inner join skips those recrods, so you only get records from lefttable with matching data in righttable.
outer join means you get all data from all tables and nulls in either the left or right fields, where either right or left data is missing.

A where clause condition acts like a join condition of an inner join.

If you do an outer join you get the same or more data, but not less data, so i doubt that's what you want. The advantage of joins in that situation with 7 tables is you have more control about what is taken as a filter of the final result (where) and what is a join condition (on), also you can use left, inner or outer join. right join is another option you can always turn into a left join, as it's just a matter of the join order.

Bye, Olaf.

Author

Commented:
Guys,

Thanks to all for the input.    Olaf, I do understand what the purpose of the joins are, but didn't have the syntax correct.   Pcelba SQL statement was correct with  some modifications that I missed.

Basically, if I can explain it, I was receiving records for the column b.item and b.amount that should have been null.    I had did the following,

LEFT JOIN billitem b ON s.sampleid = b.sampleid ;  was using amount previously

But i was doing my inner joins in the where clause.  for whatever reason foxpro didn't like that.  I believe this was because i was still designating my tables in the from clause

Author

Commented:
Thanks for ALL of the responses, they were all helpful.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial