[Zope-DB] how to complement a result set

[Smith, Neil (Worcester)]

Fejes Róbert wrote:
> Cheers,
> 
> Let's say, I've got the following tables (MySQL 4.0.15):
> 
> 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
> 
> There are 12 months:) 9 of them have not passed. The result of the
> 'select m_name, c_name from costs, months, mc
> where costs.c_id = mc.c_id and
>          months.m_id = mc.m_id;'
> statement can be formatted in a web page to look like this:
> 01 gas, rent, water
> 02 rent, water
> 03 insurance, gas, rent, water
> 
> 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
> 
> I'm using zope 2.6.1-132. <dtml-var c_name null="n/a"> or <dtml-var
> c_name missing="n/a"> did not do the trick. Is it a question of SQL
> and/or <dtml ...> staff and/or scripting in Python?
> 
> How would you solve this?
> 
> Thank you very much, indeed.
> -- 
> Bye: FeRó
> //SuSE 9.0 - a most foolproof OS

You need an outer join in your SQL.

-- 
Neil




**********************************************************************
This e-mail is provided for general information purposes only and does not constitute investment or transactional advice. For  the avoidance of doubt the contents of this email are subject to contract and will not constitute a legally binding contract.

The information contained in this e-mail is confidential and intended only for the use of the addressee. If the reader of this message is not the addressee, you are hereby notified that you have received this e-mail in error and you must not copy, disseminate, distribute, use or take any action as a result of the information contained in it.
    
If you have received this e-mail in error, please notify postmaster at npower.com (UK 01384 275454) and delete it immediately from your system.
    
Neither Npower nor any of the other companies in the RWE Innogy group from whom this e-mail originates accept any responsibility for losses or damage as a result of any viruses and it is your responsibility to check attachments (if any) for viruses.
Npower Limited Registered office: Windmill Hill Business Park, Whitehill Way, Swindon SN5 6PB. Registered in England and Wales: number 3653277.  This e-mail may be sent on behalf of a member of the RWE Innogy group of companies.
**********************************************************************