Not getting the desired output

[tekpr00]

Hello I have 3 tables with sample data

USERS table
table User_id (PK) (generated via a sequence),Email,Gender,Age,Name
1 a@a.com m 30 rob
2 a@a.com m 31 robert
3 b@b.com f 18 lucie
4 b@b.com f 22 lulu
5 c@c.com m 10 kim
6 c@c.com f 18 kim
7 c@c.com f 08 kim
8 d@d.com f 18 JJ
9 d@d.com m 22 Jay
10 e@e.com f 88 Bill
11 e@e.com f 88 Will
12 e@e.com f 60 Will
13 f@f.com m 70 George

table SUBSCRIPTIONS columns: SUbscription_id (PK) (generated via a sequence) user_id (UK) (FK from users) subscription_type (UK) active_indicator
1 2 Magazine Yes
2 3 Music CD No
3 3 Magazine Yes

table TRANSACTIONS subscription_id (PK) (FK from subscriptions) action (PK) timestamp (PK)
1 Renewal 2002-sep-10
2 Renewal 2002-Jan-01
2 Cancellation 2002-Feb-01


The selection criteria is to limit the list to users which never subscribed to
anything; or;
users with inactive subscriptions; or;
users with active subscriptions that renewed between Sep 1st and sep 30th of any year

Here is my attempt
select distinct u.email, u.name, u.gender, u.age, s.active_indicator, t.timestamp
from users u, subscriptions s, transactions t
where s.active_indicator = 'No' OR
(s.active_indicator = 'Yes' AND t.action = 'Renewal') AND
(t.timestamp >= to_date('2000-09-01', 'yyyy-mm-dd')AND t.timestamp <= to_date('2000-09-30', 'yyyy-mm-dd'))


The output was supposed to be
a@a.com m 31 robert
b@b.com f 22 lulu
c@c.com f 08 kim
d@d.com m 22 Jay
e@e.com f 60 Will

However, I am not getting the desired output, please help

 

[r937]

you forgot to join the tables properly

SELECT u.email
     , u.name
     , u.gender
     , u.age
     , s.active_indicator
     , t.timestamp
  FROM users u
LEFT OUTER
  JOIN subscriptions s
    ON s.user_id = u.user_id
LEFT OUTER
  JOIN transactions t
    ON t.subscription_id = s.subscription_id 
 WHERE s.user_id IS NULL [blue]-- never subscribed to anything[/blue]
    OR s.active_indicator = 'No' [blue]-- inactive subscriptions[/blue]
    OR (
       s.active_indicator = 'Yes' 
   AND t.action = 'Renewal'
   AND TO_DATE(t.timestamp,'MON') = 'SEP' [blue]-- renewed in Sept of any year[/blue]
       )

[red]please note, for oracle sql you really should be posting in the oracle forum[/red]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon