----- Original Message ----- From: Alan Pogrebinschi <alanpog@empresa.net> To: <zope@zope.org> Sent: Tuesday, November 23, 1999 3:47 AM Subject: [Zope] Joins can be tricky (was "how to join two table?") | When you join two tables which have column names in common and do select | both columns from each table than Zope raises an error: | | | Error, exceptions.ValueError: Duplicate column name, ID | | | In this case "ID" was a common column name among the two tables. What | happens is logical and expected, Zope cannot deal with two variables with | the same name in the same namespace! This should probably be handled by zope as it is by SQL: the names of the variables become: tablename.columnname (clients.id and columns.id in the example below). No ? | | The solution was pointed by Andy Dustman, and it works well for MySQL at | least: use the "as" syntax to rename columns names. Example: | | Instead of: | | select clients.id, columns.id | from clients,products | | you can use: | | select clients.id as clients_id,products.id as products_id | from clients,products | | This way you get unique names and Zope stops to complaint. | | I don't know if this is mysql-specific idiom or official SQL language, but | some variant may exist for most databases. The rename is a part of the SQL standard and supported by all (that I know ;-) rdbms - Oracle, MSSQL, Sybase, DB2. | | I am thinking about writing a small how-to about this, any suggestion? There you go... | Alan Shalabh