SELECT SQL and free tables

[SitesMasstec]

Hello colleagues!

I am trying to SELECT data from two free tables, but I am not achieving to do this.

I question has arisen: Is it possible to use the code bellow (taken from What's New in Nine...) with free tables only? Or must I stick the tables in a Database?

SELECT CustLast.Customer_ID, Product_ID ; 
FROM Order_Line_Items OLILast; 
JOIN Orders OrdLast; 
ON OLILast.Order_ID = OrdLast.Order_ID ; 
JOIN Customer CustLast; ON OrdLast.Customer_ID = ; 
CustLast.Customer_ID ; 
WHERE OrdLast.Order_Date = ; 
(SELECT MAX(Order_Date) ; 
FROM Orders Ord ;
WHERE OrdLast.Customer_ID = ; 
Ord.Customer_ID ) ; 
INTO CURSOR CustProducts

Thank you.

 

[Joe Crescenzi]

If your SELECT commands are not working, it's because VFP can't find your tables, so you can either configure a path or open the tables before calling the SELECT.

Technically, you can also include the path in the SELECT (SELECT * from MyFolder\MyTable), but that's bad code.

 

[SitesMasstec]

Hello Joe!

Well, I have written SELECT SQL commands to select data from a single table, for many years. Now I will have to SELECT data based on 2 tables.

My question is: SELECT SQL commands work for free tables or only with tables inside a Database?

Thank you.

 

[igorsandler]

In my opinion, you can do anything you want with free tables as long as you can open them. In other words, if you free tables located in D:\TEMP\free_table1, D:\TEMP\free_table2, you need to open them first:
use D:\TEMP\free_table1 in 0
use D:\TEMP\free_table2 in 0

Select;
free_table1.*,;
free_table2.*;
from free_table1;
JOIN free_table2

I do it all the time as long as you know where free tables are

 

[SitesMasstec]

So why is this not working?

SELECT 0
USE RESERVAS ORDER TAG LOCALIZA

SELECT 0
USE PASSAGEIROS ORDER TAG LOCALIZA

SELECT LOCALIZA, EMISSAO, CODAGENTE, NOMAGENTE, NOMECIA, NOMENAV, NUMCAB, TIPCATCAB, DATASAIDA, VENDEDOR, VOUCHER FROM RESERVAS ;
  RIGHT JOIN PASSAGEIROS ;
  ON RESERVAS.LOCALIZA = PASSAGEIROS.LOCALIZA ;
  WHERE (RESERVAS.EMISSAO>=XQualPDEmissao AND RESERVAS.EMISSAO<=XQualUDEmissao) ;
  INTO CURSOR curReservas READWRITE

The following error appeared:


In the RESERVAS.DBF table there is only one record for each LOCALIZA data (may I call it unique?) in the field.

In the PASSAGEIROS.DBF table there are up to 5 records with the same LOCALIZA data.

Thank you.

 

[igorsandler]

You need to qualify LOCALIZA with table name. This field seems belong to both tables:
Select;
free_table1.LOCALIZA,;
free_table2.LOCALIZA AS LOCALIZA_2

Look at the FoxPro Help, it should say the same thing

 

[SitesMasstec]

Igor:

After the change you suggested, I got another error:

 

[Joe Crescenzi]

When a SQL command is run against 2 or more tables, you have to ensure each field / column is unique so the parser knows which one you are referring to.

So, if a field exists in both tables, you need to specify the table or alias every time you use it, not just in the column list as you showed in your screenshot, but also in the join conditions. When all the fields are unique, you can skip the table names, but some people just make it a habit of using the prefixes just to make it readable, and to make it easier to add more columns later without overlapping column names.

For example:

SELECT ;
   Table1.Id;
   ,Table1.Name;
   ,Table1.Address;
  ,Table2.InvoiceNo;
  ,Table2.InvDate;
FROM Table1;
LEFT JOIN Table2 on Table1.ID = Table2.ID;
INTO CURSOR MyResult

In the example above, ID is common to both tables, so I only included the one from the first table, and even though the other fields may be unique, showing the table names makes it a lot easier to understand where each column is coming from, and as a bonus, if there are overlaps, it will implicitly pull the one you needed.

 

[Chriss Miller]

You join a table, not a field. So don't apply the field name to where you are referring to tables, that's nonsense. You want to join the passageiros table to the reservas table, you don't want to and can't even if you would want to join passageiros.localiza. You where told to uniquely qualify the name of fields, but names of tables are already unique, there's the need to turn localiza to either reservas.localiza or passageiros.localiza. but there's no need to specify passageiros.localiza when a table is meant. You always join y table and you pick fields from the joined table, but not in the JOIN clause, you put fields into the SELECT fieldlist before the FROM. Fields then can play a role in the join condition, but you don't join fields to a table, you always join tables.

SELECT RESERVAS.LOCALIZA, EMISSAO, CODAGENTE, NOMAGENTE, NOMECIA,;
NOMENAV, NUMCAB, TIPCATCAB, DATASAIDA, VENDEDOR, VOUCHER;
FROM RESERVAS ;
RIGHT JOIN PASSAGEIROS ON RESERVAS.LOCALIZA = PASSAGEIROS.LOCALIZA ;
WHERE (RESERVAS.EMISSAO>=XQualPDEmissao AND RESERVAS.EMISSAO<=XQualUDEmissao) ;
INTO CURSOR curReservas READWRITE

And when other fields also exist in both tables, you need to qualify the table, even if values are the same in joined tables. If you have many of these fields, that points out bad table structure and redundant data.