2

So I have to make a query to return all the reciept numbers that don't contain an "apple" item.

The data is as follows. EG If you go to the shops and buy apples and bananas the data will be:

(table reciepts)
recieptNumber      productCode
12345              9999
12345              8888

(table products)
productCode        productName
9999               Apples
8888               Bananas

I was thinking of:

SELECT reciepts.recieptNumber
FROM reciepts JOIN products
ON reciepts.productCode == products.productCode
WHERE products.productName == "Apples"
GROUP BY reciepts.recieptNumber
HAVING COUNT(*) == 0;

But I now know that having doesn't work with count = 0 because there's nothing to count.

Any suggestions?

GMB
  • 216,147
  • 25
  • 84
  • 135
user13292868
  • 87
  • 1
  • 7

5 Answers5

3

Your approach is on the right track :-)

SELECT reciepts.recieptNumber
FROM reciepts 
-- switch to Outer Join
LEFT JOIN products
ON reciepts.productCode = products.productCode
-- this will result in a NULL row when there's no Apple in the receipt
-- otherwise a row with 'Apples'
AND products.productName = 'Apples' 
GROUP BY reciepts.recieptNumber
-- if there's only a NULL you found the matching receipt 
HAVING COUNT(products.productCode) = 0;
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • does it return a null row though? what if the row is bananas or something else? – user13292868 Sep 11 '20 at 08:13
  • Upvoted! This is the same idea as my answer, but better (no self join) – Pepper Sep 11 '20 at 08:15
  • #1 removes the non-NULL rows in HAVING because the `COUNT(products.productCode)` returns 0 for NULL. #2 simply checks for the NULL – dnoeth Sep 11 '20 at 08:18
  • Sorry... I'm really confused. The reciept will always have a product code, even if it's not apples (Bananas, Pineapples, Berries, etc). I don't understand how the left join converts non-apples to NULL. – user13292868 Sep 11 '20 at 08:22
  • Because the join has 2 conditions: productCode must match and productName must be Apples. And since it is a `LEFT` join, the result (for the product table column) will be a row matching both conditions if it exists, or a row full of `null` values – Pepper Sep 11 '20 at 08:22
  • `products.productName = 'Apples' ` is part of the join-condition. Due to the Outer Join every receipt is returned, but only a row containing 'Apples' will be joined. If there's no Apples you still get a row with NULL in it. Run #2 it without the final WHERE – dnoeth Sep 11 '20 at 08:25
  • Number two didn't idk why but thanks sm #1 is perfect! – user13292868 Sep 11 '20 at 08:27
  • I think it's because from my understanding number two just returns any row that doesn't have an apple. Even if one row of a reciept doesn't have an apple, the others still can. – user13292868 Sep 11 '20 at 08:34
  • it's still returning multiple NULL's for each reciept. because reciept can have multiple rows as i showed above in my question. but don't worry about it, number one is great – user13292868 Sep 11 '20 at 08:42
  • Of course, you're correct. I'll remove my 2nd answer – dnoeth Sep 11 '20 at 08:50
1

You can use not exists

SELECT reciepts.recieptNumber
FROM reciepts JOIN products
ON reciepts.productCode = products.productCode
where not exist 
  (select 1 from products p where products.productcode=p.productcode 
          and p.productName='Apple')

you can try the alternative -

SELECT reciepts.recieptNumber
    FROM reciepts JOIN products
    ON reciepts.productCode = products.productCode
group by reciepts.recieptNumber
having max(productName)<>'Apple' and min(productName)<>'Apple'
Fahmi
  • 37,315
  • 5
  • 22
  • 31
0

Starting from your current query, you can use a conditional count in the having clause:

SELECT r.recieptNumber
FROM reciepts r 
INNER JOIN products p ON r.productCode = p.productCode
GROUP BY r.recieptNumber
HAVING MAX(p.productName = 'Apples') = 0;

Side notes:

  • use = for equality conditions rather than ==

  • use single quotes for strings - in standard SQL, double quotes stand for identifiers (such as column or table names) - although some databases implement this differently

  • table aliases make the query easier to write and read

GMB
  • 216,147
  • 25
  • 84
  • 135
0

You could get all receipt with apples, and then exclude them using a LEFT JOIN

SELECT reciepts.recieptNumber
FROM reciepts r
LEFT JOIN (
  SELECT reciepts.recieptNumber
  FROM reciepts JOIN products
  ON reciepts.productCode == products.productCode
  WHERE products.productName == "Apples"
) r_apples
ON r.recieptNumber = r_apples.recieptNumber
WHERE r_apples.recieptNumber IS NULL
Pepper
  • 587
  • 4
  • 12
0

Please, try with below solution:

SELECT DISTINCT reciepts.reciepts.recieptNumber FROM reciepts 
WHERE  reciepts.productCode not in(
 select DISTINCT products.productCode from products where products.productName = 'Apples')

Second way is:

SELECT reciepts.reciepts.recieptNumber, sum(case when products.productName = 'Apples' then 1 else 0 end) as total_apple FROM reciepts 
LEFT OUTER JOIN products on reciepts.productCode =  products.productCode 
GROUP BY  reciepts.reciepts.recieptNumber
HAVING total_apple = 0
Vikram Jain
  • 5,498
  • 1
  • 19
  • 31