Re: View onto tables w/ identical key names
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 (long 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 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View onto tables w/ identical key names
Martijn Tonies wrote: 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. i'll try that programmatically, no way am i typing close to 1000 field names into the query browser ;-) thanks les schaffer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View onto tables w/ identical key names
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. i'll try that programmatically, no way am i typing close to 1000 field names into the query browser ;-) Get yourself a GUI tool with which you can simply drag if a list of fields :-) I know one ... Database Workbench - www.upscene.com ;-) 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 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
View onto tables w/ identical key names
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 (long 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
