LEFT JOIN: Wrong results when left table is empty

Hi everyone,

I have got a problem with a TQuery and an SQL-Statement doing a left
I have two tables Paradox-Tables 'Machines.DB' and 'Suppliers.DB':

    Machines                    Suppliers
    --------                    ---------
    ID (Key)                    Name
    Supplier    ----------->    ID (Key)
    Name                        ...

In my Grid I am using the Query to display all the machines with their
suppliers. Of course I want a machine being displayed, even if it has no
supplier assigned. I am doing this with the following left join:

   SELECT M.ID, M.Name, S.Name as SupplierName
   FROM ("Machines.DB" M left join "Suppliers.DB" S on M.Supplier=S.ID)

This works fine, except when there is no record in the table 'Machines'.
Then the Select-Statement does not return an empty result, but one
for each supplier, with M.ID and M.Name being NULL.
Say we have 4 Suppliers called 'A-Company', 'B-Company', 'C-Company' and
'D-Company', then my Grid shows the following:

   M.ID   M.Name     S.Name
   ----   ------     ------

(This looks, as if it would have done a RIGHT join instead of a LEFT

As soon as I add one Machine (e.g. ID=1, Name='dryer', Supplier=3),
it shows the correct result again:

   M.ID   M.Name     S.Name
   ----   ------     ------
   1      dryer      C.Company

This is really driving me crazy! Please help me.


--------------  Ich habe Windows 95  (flames welcome)  -----------------
       Christian Kaupa                              Tel:  ++49-8531/8540
       ka...@fmi.uni-passau.de                      Fax:  ++49-8531/8538