2

I have two tables with simple relation. One table is list of sales. The second table is list of products. Relation is that in the Sales table is a Product ID that points to the given product.

I need to retrieve the details on the products that were never sold.

Here are links to the two tables: Table with Products & Table with Sales

If you don't want to click it, the tables look like this:

**SALES** id sale_id product_id quantity **PRODUCTS** id name category_id stock brand_id price color warranty

So far I have this:

SELECT products.id FROM products LEFT JOIN sale_products ON sale_products.product_id = products.id WHERE products.id NOT IN (sale_products.product_id) GROUP BY products.id

But this doesn't retrieve anything, although if I take out the NOT then I get all the 17 IDs of the 17 sold items...

So I'd say it either has to be changed around or done with completely different approach..

Any help is much appreciated and welcome.

Improve this question

2 Answers 2

Reset to default
1

Try this:

 SELECT products.id FROM products LEFT JOIN sale_products ON sale_products.product_id = products.id WHERE sales_products.product_id IS NULL GROUP BY products.id

You are asking for all products and for each product, either get a sales record or if no sales record found, return NULL for each column in the sales_product table.

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

Comments

1 
SELECT * FROM PRODUCTS P WHERE P.ID NOT IN ( SELECT PRODUCT_ID FROM SALES )
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.