Hi guys,
I have some problems in building a query using the EXISTS clause.
I have two tables:
CREATE TABLE accounts (
id serial NOT NULL,
legal_name character varying(255) NOT NULL,
address_1 character varying(255),
...
);
CREATE TABLE subscriptions (
id serial NOT NULL,
account_id integer NOT NULL,
date_start date NOT NULL,
...
);
I would like to know how many accounts are not subscribed to any service.
The SQL query to get that should be:
SELECT COUNT(*) FROM accounts WHERE NOT EXISTS (SELECT * FROM subscriptions
WHERE subscriptions.account_id = accounts.id)
I tried the following python code
accounts = store.find(Account,
SQL.Not(SQL.Exists(SQL.Select(Subscription.id,
Subscription.account_id==Account.id)))).count()
but it gets translated to the following SQL:
SELECT COUNT(*) FROM accounts WHERE NOT EXISTS (SELECT subscriptions.id FROM
accounts, subscriptions WHERE subscriptions.account_id = accounts.id)
which does not give me the expected result, the accounts table should be
removed from the FROM clause in the subquery.
Any suggestion?
I'm using Storm 0.17.0.99, I could upgrade if necessary, but I did not find
anything relevant in the changelog.
My database server is PostgreSQL 8.3.11.
Tnx in advance
Edoardo Serra
--
storm mailing list
[email protected]
Modify settings or unsubscribe at:
https://lists.ubuntu.com/mailman/listinfo/storm
