[support] views 'WHERE NOT EXISTS' Query

[Metzler, David]

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 at drupal.org [mailto:support-bounces at drupal.org] On
Behalf Of Jos De Graeve
Sent: Wednesday, May 12, 2010 6:13 AM
To: support at 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/ ]