List all products with no prices in MySQL

Question

This query is for a niche price comparison website.

I have 2 tables, the first lists product details and the second lists all the prices associated with the product. A product can have one or many prices.

I am looking to list all the products that have no active prices for housekeeping purposes.

It is possible to have an entry in the price table where the price = zero, in which case Prod_price_active would be set to "0". If a product does have a price, then Prod_price_active would be set to "1"

Table 1

Prod_id
Prod_name
Prod_description

Table 2

Price_id
Prod_id
Prod_price
Prod_price_active

Possible duplicate with http://stackoverflow.com/questions/18136113/inner-join-with-empty-result-from-right-table/18136209#18136209 — Wirus, Aug 08 '13 at 21:47

score 1 · Accepted Answer · answered Aug 08 '13 at 21:37

1

The simplest query to understand is:

select * from table1
where not exists (
    select * from table2
    where Prod_price_active = 1
    and Prod_id = table1.Prod_id)

This is the SQL version of the sentence "give me all products that don't have an active price"

answered Aug 08 '13 at 21:37

Bohemian

412,405
93
575
722

Why the nested queries? It might be one solution but it's better to avoid them when possible. – ktm5124 Aug 08 '13 at 21:38
1

+1 . . . for being the only correct answer. It's not worth adding the same thing with a left outer join. – Gordon Linoff Aug 08 '13 at 21:39
@ktm5124 see edits. a beginner would be able to understand this better. It will perform fine: If the query was over a transaction table with millions of rows, I wouldn't recommend this approach, but how many products can there be? – Bohemian Aug 08 '13 at 21:40
@Gordon, I think left join will perform better, as in my answer to http://stackoverflow.com/questions/18136113/inner-join-with-empty-result-from-right-table/18136209#18136209 – Wirus Aug 08 '13 at 21:51
There will always be less than 500 products, with each product having on average 5 prices. This query will only ever be run when doing house keeping. Just out of interest, what is the more efficient way of performing this query? – Simon Aug 08 '13 at 21:52
@Simon . . . If you have an index on `table2(Prod_id, Prod_price_active)`, the then performance will be fine using either method. – Gordon Linoff Aug 08 '13 at 22:12

score 0 · Answer 2 · answered Aug 08 '13 at 21:37

0

Assuming every product is in the price table, you can do an inner join.

select a.Prod_id, a.Prod_name, a.Prod_description 
from PRODUCTS a inner join PRICES b 
on a.Prod_id = b.Prod_id 
where b.Prod_price_active=0

answered Aug 08 '13 at 21:37

ktm5124

11,861
21
74
119

Sylvain Leroux · Answer 3 · 2013-08-08T21:50:46.133

0

You are looking for a way to perform an anti-join:

SELECT table1.* FROM table1
                LEFT JOIN table2 USING(prod_id) 
                WHERE Prod_price IS NULL
                OR Prod_price_active = 0

This will keep only those rows from table1 (the "product table") that don't have a corresponding entry on table2 (the "price table") or those having Prod_price_active set to 0.

edited Aug 08 '13 at 21:50

answered Aug 08 '13 at 21:41

Sylvain Leroux

50,096
7
103
125

List all products with no prices in MySQL

3 Answers3