All geek questions in one place

Nested joins hide table names

I have three tables: suppliers, parts, and types. I need to join all of them while highlighting columns with the same name (for example, "id") in 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 no such column (Parts.id)". I assume that it forgets the table names after the JOIN completes, but then how can I access the id column that refers to the Parts table?

+4
source share
2 answers

Incorrect ANSI-92 JOIN syntax - 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
+6
source

For now, you qualify the alias based union. The name of the field, you should not have problems ... for example

 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)
0
source

Source: https://habr.com/ru/post/1310037/

More articles:


All Articles