SQL return results if values are ever True

Question

I have a database with four columns. 'Date Checked', 'Shop', 'Product', and 'Stocked'. 'Date' is the date the stock was checked, 'Shop' is simply the text name of the shop, 'Product' is simply the Text Name of a Variety of products, and 'Stocked' is a Boolean on if that item is stocked or not. It looks something like:

Date        Shop    Product    Stocked
01/01/2018  Shop1   Product1    TRUE
02/01/2018  Shop1   Product2    TRUE
03/01/2018  Shop1   Product3    TRUE
04/01/2018  Shop1   Product4    FALSE
05/01/2018  Shop2   Product1    TRUE
06/01/2018  Shop2   Product2    TRUE
07/01/2018  Shop2   Product3    TRUE
08/01/2018  Shop2   Product4    TRUE
09/01/2018  Shop2   Product5    TRUE
10/01/2018  Shop3   Product2    FALSE
11/01/2018  Shop4   Product1    TRUE
12/01/2018  Shop4   Product2    TRUE
13/01/2018  Shop4   Product3    TRUE
14/01/2018  Shop4   Product4    TRUE

I need to return shop names if (and only if) Products A, B and C where EVER stocked in the shop. I'm having difficulty as the following query will return rows based on if each individual product was ever stocked, but I need it to return the shop name only if all products have ever been stocked.

USE [Worldwide_Stock]

SELECT DISTINCT
 Stock.Date AS 'Date',
 Stock.Shop AS 'Location',
 Stock.Product AS 'Item',
 Stock.Stocked AS 'Stocked'

FROM
 Stock with (nolock)

where

    Stock.Product = 'ProductOne' OR 
    Stock.Product = 'ProductTwo' OR 
    Stock.Product = 'ProductThree' OR 
    Stock.Product = 'ProductFour' OR 
    Stock.Product = 'ProductFive')

  AND 

    Stock.Stocked = 'True'


 ORDER BY Stock.Shop

I'm not interested in locations that have only had one or two in stock, and I don't care if items are currently not in stock (Or have not been in the past). In the example above, I'm only interested in returning 'Shop2' as a result, as it's the only one where all items where in stock at one stage or another.

I am looking for my result simply in the format of:

    Shop   Stocked
   ----------------
    Shop2   True

Is it possible to do this natively with SQL? I could export it to something like excel and have VBA do it, but I'd really rather not.

With that table data, specify the expected result as well. – jarlh Jan 22 '18 at 09:10 — jarlh, Jan 22 '18 at 09:10

Giorgos Betsos · Answer 1 · 2018-01-22T09:23:06.287

4

You can use the following query:

SELECT Shop
FROM mytable
WHERE Stocked = 'True' AND Product IN ('Product1', 'Product2', 'Product3')
GROUP BY Shop
HAVING COUNT(DISTINCT Product) = 3

The WHERE clause filters out irrelevant records. The HAVING clause checks whether the Shop group contains all three products.

Demo here

Edit:

If you are not interested in three specific products and you want to check for any product, then you can use the following query:

SELECT Shop
FROM mytable    
GROUP BY Shop
HAVING COUNT(DISTINCT CASE WHEN Stocked = 'True' THEN Product END) = COUNT(*)

The above query returns 'Shop' and 'Shop4' because these two shops are the only ones having all of their products in stock.

Demo here

Finally:

You can use this query:

SELECT Shop
FROM mytable    
GROUP BY Shop
HAVING COUNT(DISTINCT CASE WHEN Stocked = 'True' THEN Product END) = 
       (SELECT COUNT(DISTINCT Product) FROM mytable)

to get shops containing all kinds of products available in anyone shop. This query returns 'Shop2'.

Demo here

edited Jan 22 '18 at 09:23

answered Jan 22 '18 at 09:13

Giorgos Betsos

71,379
9
63
98

Giorgos, to throw a spanner in the works, is there a way to do it without the 'HAVING COUNT'? Or is it possible to put the 'HAVING COUNT' as part of the 'WHERE' clause? Something I didn't include above is that the exact types of products each shop **should** stock can vary, and we may not know in advance how many products total they should have. – HDCerberus Jan 22 '18 at 09:18
If you want all the product to be present, not just a specific list, you can add a subquery getting all products. `SELECT Shop FROM mytable WHERE Stocked = 'True' AND Product IN (SELECT DISTINCT Product FROM mytable) GROUP BY Shop HAVING COUNT(DISTINCT Product) = 5 ` – Pete_Gore Jan 22 '18 at 09:21
@HDCerberus Please check the edit I made. – Giorgos Betsos Jan 22 '18 at 09:23

SQL return results if values are ever True

1 Answers1