List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:July 1 2005 7:20am
Subject:Re: View onto tables w/ identical key names
View as plain text  
Les,


> 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 )
AS
select [fields]

Instead of "*". This way, you can only return a single "mat" column instead
of having that column twice in the view.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Thread
View onto tables w/ identical key namesLes Schaffer30 Jun
  • Re: View onto tables w/ identical key namesMartijn Tonies1 Jul
    • Re: View onto tables w/ identical key namesLes Schaffer1 Jul
  • Re: View onto tables w/ identical key namesMartijn Tonies1 Jul