List:General Discussion« Previous MessageNext Message »
From:Les Schaffer Date:June 30 2005 9:48pm
Subject:View onto tables w/ identical key names
View as plain text  
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)

is there a CREATE VIEW method i am missing for making this happen. yes, 
i can change all the key names to tablename_mat, but prefer not to, 
thats another long story. is there a join that makes all but one o fthe 
mat columns disappear? i have yet to find it. ( ON id.mat=dia.mat doesnt 
work either )

a corollary question: is there a limit to the number of columns in a 
VIEW? i know each InnoDB table (our engine choice for now) must be < 
1000 columns. we are good there and also we are < 8000 bytes per record 
in each table. can a VIEW be wider than 8000 bytes?

many thanks

les schaffer
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