Nested joins hide table names

Question

Hi:

I have three tables: Suppliers, Parts and Types. I need to join all of them while discriminating columns with the same name (say, "id") in the three tables. I would like to successfully run this query:

CREATE VIEW Everything AS
SELECT Suppliers.name as supplier, 
       Parts.id, 
       Parts.description, 
       Types.typedesc as type
FROM Suppliers JOIN (Parts JOIN Types ON Parts.type_id = Types.id)
ON Suppliers.id = Parts.supplier_id;

My DBMS (sqlite) complains that "there is not such a column (Parts.id)". I guess it forgets table names once the JOIN is done but then how can I refer to the column id that belongs to the table Parts?

Answer 1

As long as you are qualifying the join based on the table Alias.Field name, you should have no problem... such as

CREATE VIEW Everything AS 
      SELECT 
            Suppliers.name as supplier, 
            Parts.id, 
            Parts.description, 
            Types.typedesc as type 
         FROM 
            Suppliers,
            Parts,
            Types
         WHERE
                Supplier.ID = Parts.Supplier_ID
            AND Parts.Type_ID = Types.ID
         ORDER BY
            (whatever columns)

Answer 2

Your ANSI-92 JOIN syntax is incorrect - use:

CREATE VIEW Everything AS
  SELECT Suppliers.name as supplier, 
         Parts.id, 
         Parts.description, 
         Types.typedesc as type
    FROM Suppliers 
    JOIN Parts ON Suppliers.id = Parts.supplier_id
    JOIN Types ON Parts.type_id = Types.id