Issue with Oracle SQL Query

Question

Here is my sample data structure and sample data. What I am trying to accomplish here is to not show customers who have a subscriber record existing with a type of 'Subscriber'. You will see in the data set that Eli Manning has two subscription records. One is of type 'Owner' and the other is of type 'Subscriber'. So, he should not be in my results because there is an 'SUBSCRIBER' record instance. Odell Beckham Jr. has only one record with type of 'Owner', so he should be showing.

I have tried to use this query, but the results return customer Saquan Barkley. You will see that this customer has a 'Subscriber' record in the Subscribers table, so my sql is not working as expected. Any help would be much appreciated.

My Query:

select distinct
    a.customer_id,
    a.fst_name,
    a.last_name,
    a.email,
    b.subscription_type
from
    customers a,
    subscriptions b
where
    a.customer_id <> (select customer_id from subscriptions
                      where subscription_type <> 'SUBSCRIBER')
    AND b.subscription_type <> 'SUBSCRIBER'
order by customer_id asc;

Tables & Data:

DROP TABLE CUSTOMERS;
DROP TABLE SUBSCRIPTIONS;

CREATE TABLE "CUSTOMERS" 
   (    "FST_NAME" VARCHAR2(50 BYTE), 
    "LAST_NAME" VARCHAR2(100 BYTE), 
    "CUSTOMER_ID" NUMBER NOT NULL ENABLE, 
    "EMAIL" VARCHAR2(150 BYTE), 
     CONSTRAINT "CUSTOMERS_PK" PRIMARY KEY ("CUSTOMER_ID"));

  CREATE TABLE "SUBSCRIPTIONS" 
   (    "ID" NUMBER NOT NULL ENABLE, 
    "CUSTOMER_ID" NUMBER NOT NULL ENABLE, 
    "SUBSCRIPTION_TYPE" VARCHAR2(20 BYTE), 
    "SERIAL_NUMBER" VARCHAR2(50 BYTE), 
     CONSTRAINT "SUBSCRIPTIONS_PK" PRIMARY KEY ("ID")); 

INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Eli', 'Manning', '1', '[email protected]');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Odell', 'Beckham Jr.', '2', '[email protected]');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Saquan', 'Barkley', '3', '[email protected]');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Evan', 'Engram', '4', '[email protected]');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Nate', 'Solder', '5', '[email protected]');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Patrick', 'Omameh', '6', '[email protected]');
INSERT INTO subscriptions (id, customer_id, subscription_type, serial_number)
VALUES ('1', '1', 'SUBSCRIBER', 'ASDF1234556');
INSERT INTO subscriptions (id, customer_id, subscription_type, serial_number)
VALUES ('2', '1', 'OWNER', 'ASDF1234556');
INSERT INTO subscriptions (id, customer_id, subscription_type, serial_number)
VALUES ('3', '2', 'OWNER', 'ASDF987657');
INSERT INTO subscriptions (id, customer_id, subscription_type, serial_number)
VALUES ('4', '3', 'SUBSCRIBER', 'ASDF11223344');
COMMIT;

Tip of today: Switch to modern, explicit JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh
– jarlh, Commented Aug 23, 2018 at 14:06
Another tip: Have table aliases that make sense, like 'c' for 'customers'. (Instead of just a, b, etc.) — jarlh
– jarlh, Commented Aug 23, 2018 at 14:08
from customers a, subscriptions b is from customers a cross join subscriptions b essentially. You are applying no criteria on how to join the two tables (such as a.customer_id = b.customer_id). So you are combining all customers with all subscriptions. You really shouldn't use this syntax. It was replaced with explicit joins in standard SQL 1992 (though it took Oracle nine years to finally adopt this in Oracle 9i). DISTINCT is typical sign for badly written queries. Instead of applying it right away, think about what makes its use necessary. — Thorsten Kettner
– Thorsten Kettner, Commented Aug 23, 2018 at 14:21
Do you need b.subscription_type in your output? This can of course lead to multiple rows for the same customer. If you need it, wouldn't you rather like a list (a comma separated string with the types) and only one row per customer? If you don't need it, then you don't have to join with the subscriptions table at all. — Thorsten Kettner
– Thorsten Kettner, Commented Aug 23, 2018 at 14:25

rs. · Accepted Answer · 2018-08-23 14:38:31Z

1

The query you have is close to what you want, instead of using <> try not in, the subquery may return multiple rows and you cannot apply <> to it

Ex:

select distinct
    a.customer_id,
    a.fst_name,
    a.last_name,
    a.email,
    b.subscription_type
from
    customers a
    inner join subscriptions b on a.customer_id = b.customer_id
where b.subscription_type <> 'SUBSCRIBER' 
and a.customer_id not in
(
  select customer_id from subscriptions where subscription_type = 'SUBSCRIBER'
)
order by customer_id asc;

edited Aug 23, 2018 at 14:38

answered Aug 23, 2018 at 14:16

rs.

27.6k13 gold badges71 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Goran Kutlaca · Accepted Answer · 2018-08-23 14:23:32Z

1

Try the following code:

  SELECT a.customer_id, a.fst_name, a.last_name, a.email, b.subscription_type
    FROM customers a 
    LEFT JOIN subscriptions b ON (b.customer_id = a.customer_id)
   WHERE a.customer_id NOT IN (SELECT customer_id
                                 FROM subscriptions
                                WHERE subscription_type = 'SUBSCRIBER')
ORDER BY a.customer_id ASC;

Other than changing your code to include NOT IN instead of <> (which then includes changing your subquery condition to include all customer that do have type 'SUBSCRIBER' among their types), I also switched your syntax to use explicit JOIN. Adding to that, you don't need DISTINCT if you join your tables correctly.

edited Aug 23, 2018 at 14:23

answered Aug 23, 2018 at 14:16

Goran Kutlaca

2,0241 gold badge14 silver badges18 bronze badges

Comments

tamla83 · Accepted Answer · 2018-08-23 14:22:02Z

0

You mean something like this?

    select distinct a.customer_id, a.fst_name, a.last_name, a.email,
                b.subscription_type
  from customers a, subscriptions b
 where a.customer_id = b.customer_id 
   and a.customer_id not in (select customer_id from subscriptions
    where subscription_type = 'SUBSCRIBER')
 order by customer_id asc

answered Aug 23, 2018 at 14:22

tamla83

3904 silver badges15 bronze badges

Collectives™ on Stack Overflow

Issue with Oracle SQL Query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related