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

 

[PKunder]

Hi

IF !USED("CustLast")
USE SHARED "Customer" AGAIN ALIAS "CustLast" IN 0
ENDIF
SELECT CustLast


IF !USED("Order_Line_Items")
USE SHARED "OrderLine" AGAIN ALIAS "Order_Line_Items" IN 0
ENDIF
select Order_Line_Items

IF !USED("Orders")
USE SHARED "Order" AGAIN ALIAS "Orders" IN 0
ENDIF
select Orders

....

 

[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. What the error points out that the join expects to join a dbf file, not a field. 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 a) not field names and b) already unique in themselves, there's the need to turn localiza to either reservas.localiza or passageiros.localiza but there's no need to turn passageiros to passageiros.localiza when the passageiros table is meant. You always join a 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 and will play a role in the join condition (that the ON clause of the join), but you don't join fields to a table, you always join tables, you join them based on matches in fields of the joined tables and you join them to enable picking/selecting fields from that joined tables, too, or at least make where conditions based on fields of the joined table, if they don't get into the final result. If none of both you would need to ask yourself why you join, but I guess that's not the question.

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. You never need to turn a table name into a field name, though. Why? Where tables are meant, tables are meant, not fields.

 

[Joe Crescenzi]

In summary, your problem is not a question of whether your SQL SELECT is failing because you are using free tables vs a database container.

Your problem is understanding the syntax of the JOIN condition. ...JOIN ON TableName1.FieldName1 = TableName2.FieldName2.

As mentioned earlier, you don't need to specify the table names if the column names are unique, but I prefer to include them for clarity.

 

[igorsandler]

Totally agree with all comments above. I don't want to sound like an a..s but it seems you are not very comfortable with writing your own SQL statements so try FoxPro View Wizard, that's how you can learn to write your won SQL. The only problem with the wizard(at least in FoxPro 7 I use), you cannot do more that 3 JOINs, I think, haven't used wizard for more than 25 years

 

[SitesMasstec]

Hello colleagues!

I changed my code and almost got the result I expected:

SELECT 0
USE RESERVAS ORDER TAG LOCALIZA

SELECT 0
USE PASSAGEIROS ORDER TAG CHAVEPAX

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

SELECT RESERVAS
USE
SELECT PASSAGEIROS
USE

SET REPORTBEHAVIOR 90
DO FORM RELRESERVAS6      && 22/07/2025 
SET REPORTBEHAVIOR 80

RESERVAS.DBF has a unique data in the LOCALIZA field.
PASSAGEIROS.DBF can have up to 5 same data in the LOCALIZA field.

I need only the record in the RESERVAS.DBF table, not each in the PASSAGEIROS.DBF field:


As you can see there are repeated records from RESERVAS.DBF table, because:
PASSAGEIROS.DBF table has 2 records with data 5005147 and 5 records with data 1001258 in the LOCALIZA field.

What is wrong in my SQL command?

 

[igorsandler]

Well, use GROUP BY the key field, LOCALIZA I guess. You use SELECT DISTINCT, even though I don't like it and do no use it. You have one-to-many relationship between two tables and it seems you have duplicated on many side, not sure why. Doesn't look like normalized database to me. Your highlighted records are absolutely identical

 

[SitesMasstec]

Igor:

I have ONLY one distinct record in the RESERVAS.DBF table. Just one record have a unique data in the LOCALIZA field. Other data fields are related to Travel data, Date of reservation, Travel Agent's name, Ship company, etc.

In the PASSAGEIROS.DBF table I can have up to 5 records with the same data in the LOCALIZA field. The other fields are related to a Passenger travelling in the cabin of a ship (Passenger's name, document number, date of birth, form of payment, etc).

So, I can have one record referring to a voyage (in the RESERVAS.DBF table) and up to 5 records (in the PASSAGEIROS.DBF table) refering to people in the cabin of the ship, for example, a couple and a son, which all counts as 3 passengers (therefore 3 records in the PASSAGEIROS.DBF table).

 

[mjcmkrsr]

Hi SitesMassTec,

SQL seems to make you sweat quite a bit - see your post from Oct 2024.

My questions in this context :

Why do you want to JOIN PASSAGEIROS since you don't include any field(s) from that table ?
Why do you index the cursor without setting a tag ?
Why do you close the tables immediately after generating the cursor ?
What does "DO FORM RELRESERVAS6" do ? Shouldn't it be REPORT FORM ...

Assuming that XQUAL... are variables, I'd suggest:

SELECT RESERVAS.LOCALIZA, RESERVAS.EMISSAO, RESERVAS.CODAGENTE, RESERVAS.NOMAGENTE, RESERVAS.NOMECIA, RESERVAS.NOMENAV, ;
RESERVAS.NUMCAB, RESERVAS.TIPCATCAB, RESERVAS.DATASAIDA, RESERVAS.VENDEDOR, RESERVAS.VOUCHER FROM RESERVAS ;
WHERE BETWEEN (RESERVAS.EMISSAO , XQualPDEmissao, XQualUDEmissao) ;
ORDER BY LOCALIZA ;
INTO CURSOR curReservas READWRITE

REPORT FORM ...

hth

Good Luck

MarK

 

[SitesMasstec]

Hello Mark!

Why do you want to JOIN PASSAGEIROS since you don't include any field(s) from that table ?

Good question! Indeed I have 3 fields in PASSAGEIROS that will have a calculation to be put in the form. I am still working on the first part.

Why do you index the cursor without setting a tag and close the tables immediately after ?

Because the data in the Form has to be indexed by LOCALIZA, and if the user click on a column in the Grid, for example, EMISSAO, the order will be EMISSAO; and I will not need the tables anymore, just the Cursor.

What does "DO FORM RELRESERVAS6" do ? Shouldn't it be REPORT FORM ...

Because the data are presented in a Grid, and the user can change the order of the records by some fields, clicking on the Header of the desired column.
And if the user click on the field LOCALIZA of a record , the program will open a new screen with other data.

Your suggestion:

...
WHERE BETWEEN (RESERVAS.EMISSAO , XQualPDEmissao, XQualUDEmissao)  ;
ORDER BY LOCALIZA ;

...WHERE BETWEEN... Yes, you are right, I am bad accostumed in old style

...ORDER BY LOCALIZA ... Yes, but maybe I will have to use CHAVEPAX as the tag, which contains LOCALIZA-1, LOCALIZA-2... LOCALIZA-5 (-n is the Passenger number in the ship cabin). I am still not sure about using CHAVEPAX as a tag.

Thank you.

 

[igorsandler]

Igor:

I have ONLY one distinct record in the RESERVAS.DBF table. Just one record have a unique data in the LOCALIZA field. Other data fields are related to Travel data, Date of reservation, Travel Agent's name, Ship company, etc.

In the PASSAGEIROS.DBF table I can have up to 5 records with the same data in the LOCALIZA field. The other fields are related to a Passenger travelling in the cabin of a ship (Passenger's name, document number, date of birth, form of payment, etc).

So, I can have one record referring to a voyage (in the RESERVAS.DBF table) and up to 5 records (in the PASSAGEIROS.DBF table) refering to people in the cabin of the ship, for example, a couple and a son, which all counts as 3 passengers (therefore 3 records in the PASSAGEIROS.DBF table).

SitesMassTec,

So, RESERVAS.DBF is a parent(one) and PASSAGEIROS.DBF is a child(many). If you have more than one record in the child table with the same key as parent, you will have 5 records. If those 5 records are identical, that's what you are going to have. I mean, it's a bit hard to help you without seeing both tables but what I don't understand is why LOCALIZA = 5005147 shows 5 duplicate records on your output? It's like the same person Ana Maria has two tickets for herself in one reservation. There's got to be a data to break a tie: ticket number, person name, whatever. Either you didn't include the field that makes every child record unique or you have a bad data.
And, like Mark had mentioned above, why do you have FROM PASSAGEIROS.dbf is there are no fields from it?.
Try this and see if PASSAGEIROS.LOCALIZA AS PASSAGEIROS_LOCALIZA
SELECT;
PASSAGEIROS.LOCALIZA AS PASSAGEIROS_LOCALIZA,;
RESERVAS.LOCALIZA,;
RESERVAS.EMISSAO,;
RESERVAS.CODAGENTE,;
RESERVAS.NOMAGENTE,;
RESERVAS.NOMECIA,;
RESERVAS.NOMENAV, ;
RESERVAS.NUMCAB,;
RESERVAS.TIPCATCAB,;
RESERVAS.DATASAIDA,;
RESERVAS.VENDEDOR,;
RESERVAS.VOUCHER;
FROM RESERVAS ;
RIGHT JOIN PASSAGEIROS;
ON RESERVAS.LOCALIZA = PASSAGEIROS.LOCALIZA ;
WHERE (RESERVAS.EMISSAO>=XQualPDEmissao AND RESERVAS.EMISSAO<=XQualUDEmissao) ;
GROUP BY RESERVAS.LOCALIZA;
INTO CURSOR curReservas READWRITE

Your data should have records like that:
RESERVAS.LOCALIZA = 1
PASSAGEIROS.LOCALIZA = 1 PASSAGEIROS.Passenger1
PASSAGEIROS.LOCALIZA = 1 PASSAGEIROS.Passenger2
PASSAGEIROS.LOCALIZA = 1 PASSAGEIROS.Passenger3

 

[Chriss Miller]

RESERVAS.LOCALIZA = PASSAGEIROS.LOCALIZA is a valid and sensible join condition, but if the single table query solves it I just have to give up trying to teach anything. I also don't remember any previous discussions about the RESERVAS and PASSAGEIROS tables and I don't see why they would have even more fields in common.

There is one more case where a join makes sense even if no field of the joined table is added into the SELECT field list or to filter with a WHERE clause. A join, specifically an inner join, can filter data in itself, too and just be used for that purpose.

I just critizize how you agree with anything that removes errors, you have to know what result you want and what it has to fulfill, a code not erroring is not a prove it's correct, it's just not throwing errors, could still miss records or have too many.