[Zope] Select in ZSQL

[jan@haul.de]

Diego Rodrigo Neufert wrote:
> 
> Hi...
> 
> I'm having problems with this select statement in ZQL using a MySQL db:
> select table1.id, table2.id from table_x table1, table_y table2
> 
> I got this error:
> Error, exceptions.ValueError: Duplicate column name, id

Because you *have* identical (duplicate) column names, namely,
'id'.

If you just forgot a join condition, you'd write

   select table1.id
   from table1, table2
   where table1.id = table2.id;

thus ypu have only one 'id' column, as is proper (because they
are identical anyway, by virtue of the join condition - the where
clause.)

If, on the other hand, you really want the cartesian product (*),
then you would write

   select table1.id as id1, table2.id as id2
   from table1, table2;

thereby giving different names to the two 'id' columns.

HTH,
Jan

(*) e.g., let's assume both table have only the id column. Table1
holds the rows '1' and '2' (values of the column), table2 holds
the rows '3', '4', and '5'.
Then,

   select table1.id as id1, table2.id as id2
   from table1, table2;

would render

   id1	id2
   ---  ---
   1    3
   1    4
   1    5
   2    3
   2	4
   2	5

i.e., all possible combinations of the values in both tables,
called the cartesian product. For larger tables, this gets huge
soon :-)

More on this in any decent text on SQL.