[Zope] Joins can be tricky (was "how to join two table?")

[Shalabh Chaturvedi]

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