I'm not aware of a way to do this in views, but I'm working on a module called Forena that's a general reporting engine that could be used for this. It's still under development, but might be able to get you what you need.

I'd wait for better reponse for this list, but if you get desparate, file a support request on the Forena issue queue and I'll help you walk through getting a report set up.

Dave

-----Original Message----- From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Jos De Graeve Sent: Wednesday, May 12, 2010 6:13 AM To: support@drupal.org Subject: [support] views 'WHERE NOT EXISTS' Query

Hi,

I am building a drupal site with views and CCK that is used to manage a member list and payments of these members. So i have a content type "member" and a content type "payment". The payment content type has a node reference field to the "member" content type.

Now i want to build a view that lists members that have not payed in this year.

Basically, this SQL query explains (and gives me the right results) what i am trying to query:

SELECT * FROM content_type_member WHERE NOT EXISTS ( SELECT * FROM content_type_payment WHERE content_type_member.nid=content_type_payment.field_member_nid and content_type_payment.field_jaar_value=2010 )

The problem is that i cannot figure out how to translate this query into a view.

Is this possible to build such a view ? Can anybody help me with this ?

Thanks in advance, Jos -- [ Drupal support list | http://lists.drupal.org/ ]