asked on

Foxpro 7 SELECT with JOINS

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)

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pavel Celba

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 Doschke

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.

anderdw2

ASKER

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

anderdw2

ASKER

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