Re: [Zope] how to join two table?
You do this with the SELECT statement in the database query, not with Zope or ZSQL per se. ZSQL just sends the SELECT statement to the database. Tom Passin
From: Rod Za <rodza@bhnet.com.br>
How i can join two table with ZSQL?
thank you.
Rod Za
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! 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. I am thinking about writing a small how-to about this, any suggestion? Alan
From: Rod Za <rodza@bhnet.com.br>
How i can join two table with ZSQL?
thank you.
Rod Za
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! 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.
Alan i try this with ZODBC and work.
I am thinking about writing a small how-to about this, any suggestion?
Alan
When u write, please say to me. I am new for Zope and Databases, i am trying to make a site (for a school work) and i have too much dificult to do that, if i dont have help from persons like you and Jim Sanford (thanx Jim) i never can do nothing! :-) If i can suggest somethings, i like to suggest a how-to from beggin to end (basic things from how to configure an ODBC manange to some SQL tips like that you give me). Any way, i like to thankful you for the help. Rod Za. ps. sorry for my poor english.
----- 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
participants (4)
-
Alan Pogrebinschi -
Rod Za -
Shalabh Chaturvedi -
Thomas B. Passin