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

[Alan Pogrebinschi]

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