Nested joins hide table names
- by Sergio
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?