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', 'emannning@giants.com');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Odell', 'Beckham Jr.', '2', 'beckham@giants.com');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Saquan', 'Barkley', '3', 'sbarkley@giants.com');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Evan', 'Engram', '4', 'eEngram@giants.com');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Nate', 'Solder', '5', 'nsolder@giants.com');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Patrick', 'Omameh', '6', 'pomameh@giants.com');
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;

Answer 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;

Answer 2

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.

Answer 3

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