[Zope-DB] how to complement a result set

[Andreas Kaiser]

Hi FeRó!

--On Tuesday, 9. March 2004 12:07 +0100 Fejes Róbert <pouch at freemail.hu> 
wrote:

> costs (c_id int not null primary key, c_name varchar(255) not null)
> months (m_id int not null primary key, m_name varchar(255) not null)
> mc (c_id int not null, m_id int not null, cost int)
>
> //mc.c_id and mc.m_id are foreign keys
>
>
> How to complement this result set to have a full list?
>
> 01 gas, rent, water
> 02 rent, water
> 03 insurance, gas, rent, water
> 04 not available yet
> 05 not available yet
> ...
> 11 not available yet
> 12 not available yet
>
> How would you solve this?

I am not sure wether recent MySQL versions support OUTER JOINS. If not, 
it´s
another good reason to switch to a different (read: real ;-)) RDBMS.

Your query could be like this:

select m_name, c_name
from costs, months LEFT OUTER JOIN mc ON (months.m_id = mc.m_id) LEFT OUTER 
JOIN costs ON (costs.c_id = mc.c_id)

If it´s not possible in MySQL and you are bound to this RDBMS, you
could solve your problem with a nested loop within a python script calling
multiple ZSQLMethods.

hth,
Andreas