Hi all,
I have three tables; users, propositions, and subscriptions:
users
-----
user_id user_name user_password user_email
------- --------- -------------
------------------
U000001 mike scott [EMAIL
PROTECTED]
U000002 steve wickham [EMAIL
PROTECTED]
U000003 kevin wilkinson [EMAIL
PROTECTED]
U000004 anthony thistlewaite [EMAIL
PROTECTED]
U000005 karl walinger [EMAIL
PROTECTED]
propostions
-----------
prop_id proposition user_id
------- ------------ -------
P000001 "Learn Chinese" U000001
P000002 "Boycott U2" U000002
P000003 "Come Back As A God" U000003
subscriptions
-------------
sub_id prop_id user_id
------ ------- -------
S000001 P000002 U000001 --+
S000002 P000002 U000003 |--> Everyone has decided
to boycott U2
S000003 P000002 U000004 | (Steve isn't listed
because he proposed it)
S000004 P000002 U000005 --+
S000005 P000001 U000002 -----> Steve has decided to
learn Chinese
S000006 P000003 U000005 -----> Karl wants to come
back as a God
Given a particular user, I want to return the set of 'interesting'
propositions, i.e., those which were not proposed by the user and to
which the user is not already subscribed...
For example, if Kevin performs the query, his only 'interesting'
proposition is to "learn Chinese".
The propositions marked *Interesting* constitute the desired result
set for each user:
"Learn Chinese" "Boycott U2" "Come Back As A God"
------------- ---------- ------------------
Mike Proposed Subscribed *Interesting*
Steve Subscribed Proposed *Interesting*
Kevin *Interesting* Subscribed Proposed
Anthony *Interesting* Subscribed *Interesting*
Karl *Interesting* Subscribed Subscribed
In short, returning to Kevin, I want to construct a single SQL query
that effectively subtracts B from A:
A: SELECT prop_id FROM propositions WHERE ( user_id != U000003 )
prop_id
-------
P000001 Kevin can subscribe to these
P000002 (because he didn't propose them).
B: SELECT prop_id FROM subscriptions WHERE (user_id = U000003 )
prop_id
--------
P000002 He can't subscribe to this one
(becuase he is already subscribed to it.)
Any help very much appreciated.
Sebastian
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
