[Erp5-dev]Some SQL tricks : how to find a document which is not in....

[Jean-Paul Smets]

Here is the answer:

SELECT DISTINCT catalog.id FROM catalog
       LEFT JOIN category ON category.base_uid=-625404171 AND 
category.uid=catalog.uid
       WHERE category.uid IS NULL AND portal_type='Modele'

will look for all 'Modele' documents which have not been associated through 
base_uid=-625404171 

To use it in a ZSQLMethod, use the syntax:

SELECT DISTINCT catalog.id FROM catalog
       LEFT JOIN category ON category.base_uid=<dtml-var 
"portal_categories[my_base_category].uid"> AND category.uid=catalog.uid
       WHERE category.uid IS NULL AND portal_type='Modele'

where my_base_category is a parameter of the ZSQLMethod.

JPS.

-- 
Jean-Paul Smets-Solanes 
Nexedi CEO    Tel. +33(0)6 62 05 76 14    Fax. +33(0)1 53 01 09 29

About www.nexedi.com 

Nexedi is a consulting and development services company helping small and 
medium organisations to choose open source / free software and fulfill 
their IT application needs. Nexedi is the founder of the ERP5 project, a 
Free / Open Source ERP software based on innovative technologies 
(www.erp5.org).

About www.storever.com

Storever provides a reliable source for OpenBrick, WiFi infrastructure, 
notebooks and servers preconfigured with the GNU/Linux operating system