> i am working on a database system where a number of tables (5-10) each
> with possibly hundreds of columns share an identical primary key name.
> the truth is if it weren't for a limitation in the number of columns in
> M$ Access (longgggg story: we're creating a client server scheme so they
> can get their Access DB for data analysis purposes post data
> collection)) these would be all one table.
> since i need to use MySQL tables with a similar structure (long story),
> i would like to join these tables at the hip, so to speak, with a VIEW.
> however when i try to create said VIEW from a select/join, MySQL balks
> because the primary key name is the same in each table:
> select * from id left join dia USING (mat) left join msa USING (mat)
> left join sq USING (mat)
When creating the view, use
CREATE VIEW viewname ( column names )
Instead of "*". This way, you can only return a single "mat" column instead
of having that column twice in the view.
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Database development questions? Check the forum!