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?