Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Not getting the desired output

Not getting the desired output

Not getting the desired output

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

RE: Not getting the desired output

you forgot to join the tables properly


SELECT u.email
     , u.name
     , u.gender
     , u.age
     , s.active_indicator
     , t.timestamp
  FROM users u
  JOIN subscriptions s
    ON s.user_id = u.user_id
  JOIN transactions t
    ON t.subscription_id = s.subscription_id
 WHERE s.user_id IS NULL -- never subscribed to anything
    OR s.active_indicator = 'No' -- inactive subscriptions
    OR (
       s.active_indicator = 'Yes'
   AND t.action = 'Renewal'
   AND TO_DATE(t.timestamp,'MON') = 'SEP' -- renewed in Sept of any year
please note, for oracle sql you really should be posting in the oracle forum

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close