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)
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)
Do more with
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.
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)
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)
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.
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.
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial