CODEWITHSUNDEEP
sqloracle-database
Josh King
Josh King

Reputation: 15

NOT EXIST clause

I am trying to find Products that have never been ordered. My 2 tables look like this.

CREATE TABLE Orders
(OrderNum    NUMBER(10)      NOT NULL,
 OrderDate   DATE            NOT NULL,
 Cust        NUMBER(10),
 Rep         NUMBER(10),
 Mfr         CHAR(3)         NOT NULL,
 Product     CHAR(5)         NOT NULL,
 Qty         NUMBER(5)       NOT NULL,
 Amount      NUMBER(9,2)     NOT NULL,
 CONSTRAINT OrdersPK 
      PRIMARY KEY (OrderNum));

CREATE TABLE Products
(Mfr         CHAR(3)         NOT NULL,
 Product     CHAR(5)         NOT NULL,
 Description VARCHAR2(20)    NOT NULL,
 Price       NUMBER(9,2)     NOT NULL,
 QtyOnHand   NUMBER(5),
 CONSTRAINT ProductsPK 
      PRIMARY KEY (Mfr, Product));

The code I currently have looks like this.

SELECT Mfr, Product
FROM Products
WHERE NOT EXISTS (SELECT Products.Mfr 
                  FROM Orders, Products
                  WHERE Orders.Mfr = Products.Mfr);

Although I am not getting any errors there are also no results showing up.

**EDIT: There are 26 Products and 19 of them have been ordered. I am expecting to get 7 Results but I am getting 0.

Upvotes: 0

Views: 58

Answers (3)

Dai
Dai

Reputation: 155145

An alternative is to use the set operation operator EXCEPT - as you want "the set of Products that don't exist in Orders":

SELECT
    Mfr,
    Product
FROM
    Products

EXCEPT

SELECT
    DISTINCT
    Mfr,
    Product
FROM
    Orders

You can then use this as a subquery to get full product information.

SELECT
    *
FROM
    Products
    INNER JOIN (

        SELECT
            Mfr,
            Product
        FROM
            Products

        EXCEPT

        SELECT
            DISTINCT
            Mfr,
            Product
        FROM
            Orders
   ) AS ProductsWithNoOrders ON
        Products.Mfr = ProductsWithNoOrders.Mfr AND
        Products.Product = ProductsWithNoOrders.Product

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use NOT EXISTS, but you need to compare both keys:

SELECT p.Mfr, p.Product
FROM Products p
WHERE NOT EXISTS (SELECT 1
                  FROM Orders o
                  WHERE o.Mfr = p.Mfr AND
                        o.Product = p.Product
                 );

This is a case where it makes lots of sense to have an auto generated primary key that can be used for foreign key relationships.

Upvotes: 1

Roman Marusyk
Roman Marusyk

Reputation: 24579

Try this one

SELECT Mfr, Product
FROM Products
WHERE NOT EXISTS (SELECT Orders.Mfr
                  FROM Orders
                  WHERE Orders.Mfr = Products.Mfr AND Orders.Product = Products.Product);

Upvotes: 1

Related Questions