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] -----------------------------------------------------------------------------