CODEWITHSUNDEEP
mysqlsql
user3558008
user3558008

Reputation: 3

SQL - Products with No Sales

I'm trying to list all the Products that have had no sales. I'm really close but I'm trying to use the NOT EXISTS statement to display all the ProductID's that are in the Product table, but not in the Orderline table; and hence have no sales. My code is as follows; 

SELECT product.productid
FROM product
JOIN orderline ON product.productid = orderline.productid
WHERE NOT EXISTS (

SELECT orderline.productid
FROM orderline
)
ORDER BY productid

Upvotes: 0

Views: 6223

Answers (3)

theHacker
theHacker

Reputation: 4043

You can avoid the Subselect by simply doing a LEFT JOIN:

SELECT product.productid
FROM product
LEFT JOIN orderline USING (productid)
WHERE orderline.productid IS NULL
ORDER BY product.productid

LEFT JOIN will list all rows in product even when there is no JOIN partner in orderline. In that case all columns in orderline have NULL values. These are the rows you want in the result.

Upvotes: 0

Andreas
Andreas

Reputation: 5103

If you must use not exists, try this:

SELECT *
FROM product p
WHERE NOT EXISTS (
        SELECT 1
        FROM orderline o
        WHERE p.productid = o.productid
        )
ORDER BY p.productid

Upvotes: 2

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

If you want to get the products that does not exists in orderline table you can simply use LEFT join with WHERE orderline.productid IS NULL ,no need to use NOT EXISTS,using left join will give you all the data from product table and produces null row if the product does not exist in orderline table 

SELECT p.productid
FROM product p
LEFT JOIN orderline o ON p.productid = o.productid
WHERE o.productid IS NULL
ORDER BY p.productid

Upvotes: 1

Related Questions