Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Peter99
Constraint Violating Yak Guru
498 Posts |
 Posted - 2011-08-04 : 19:21:00
|
| I have 3 tables with sample dataUSERStable User_id (PK) (generated via a sequence),Email,Gender,Age,Name1 a@a.com m 30 rob2 a@a.com m 31 robert3 b@b.com f 18 lucie4 b@b.com f 22 lulu5 c@c.com m 10 kim6 c@c.com f 18 kim7 c@c.com f 08 kim8 d@d.com f 18 JJ9 d@d.com m 22 Jay10 e@e.com f 88 Bill11 e@e.com f 88 Will12 e@e.com f 60 Will13 f@f.com m 70 Georgetable SUBSCRIPTIONS columns: SUbscription_id (PK) (generated via a sequence) user_id (UK) (FK from users) subscription_type (UK) active_indicator1 2 Magazine Yes2 3 Music CD No3 3 Magazine Yestable TRANSACTIONS subscription_id (PK) (FK from subscriptions) action (PK) timestamp (PK) 1 Renewal 2002-sep-102 Renewal 2002-Jan-012 Cancellation 2002-Feb-01The selection criteria is to limit the list to users which never subscribed toanything; or;users with inactive subscriptions; or;users with active subscriptions that renewed between Sep 1st and sep 30th of any yearThe output should be:a@a.com m 31 robertb@b.com f 22 luluc@c.com f 08 kimd@d.com m 22 Jaye@e.com f 60 Will0what is sql query to achive this.Thanks |
|
|
sql-programmers
Posting Yak Master
190 Posts |
Posted - 2011-08-04 : 22:56:13
|
| Try this script,SELECT #USERS.*,MONTH([timestamp]) FROM #USERS LEFT JOIN #SUBSCRIPTIONS ON #USERS.USER_ID=#SUBSCRIPTIONS.USER_ID LEFT JOIN #TRANSACTIONS ON #TRANSACTIONS.subscription_id = #SUBSCRIPTIONS.SUbscription_idWHERE #SUBSCRIPTIONS.USER_ID IS NULL OR #SUBSCRIPTIONS.active_indicator='Yes'OR (MONTH([timestamp]) = 9)SQL Server Programmers and Consultantshttp://www.sql-programmers.com/ |
 |
|
|
|
|
|
query result