From: Martijn Tonies (m.toniesupscene.com)
Date: Fri Jul 01 2005 - 02:20:40 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]