Wiki Home

Sql Command


Namespace: WIN_COM_API
select * from test as a, tesd as b where a.id=b.id

Drop the word as in your code and it should work

select * from test a, tesd b where a.id=b.id

Even better and clearer syntax would be

SELECT * FROM test a JOIN tesd b ON a.id=b.id

Using one-character aliases in SQL commands is a bad idea. Though it's no longer true, in some versions of VFP, this will cause confusion. Letters A through J refer to the first 10 work areas and if there are already tables open in the referenced work area, the command will not perform as expected. I know this is still true in VFP 6, but don't know when it was changed. -- Tamar Granor

In fact single letter aliases rarely make any sense. Unfortunately most examples I've seen do use single letter aliases, perhaps out of laziness, and it seems, new programmers pick that up as if it is recommended. Arbitrarily assigning A and B as aliases does not make for readable, comprehensible SQL. On a long SQL or even after a few months, you will end up having to refer back to the table declarations to determine what a, b or c mean.

select * from a inner join b on b.fk = a.pk into cursor c
has no intrinsic meaning. Use the full name of the table or a meaningful abbreviation instead of the arbitrary a, b, c.
select * from invoices inv inner join customers cus on cus.fk = inv.pk into cursor c_invoices
has much more meaning. -- Mike Yearwood

and
SELECT *
  FROM Invoices Inv
    INNER JOIN Customers Cus
    On Cus.FK = Inv.PK
  INTO CURSOR C_Invoices

is much easier to read (and, therefore, maintain). -- Art Bergquist
( Topic last updated: 2008.09.10 05:02:37 PM )