How to select grouped records only if every record in a group meets a certain criteria in MySQL?

Question

I'm using MySQL 5.0.88/Coldfusion8 and I have a table that contains products based on barcodes/EANs.

So a product 123 in size S,M,L,XL will have four records in the table

 product size  ean              qty    disregard_inventory
 123     S     1111111111111     5     0
 123     M     1111111111112     7     0
 123     L     1111111111113     1     0
 123     XL    1111111111114     2     0

Right now I'm searching this table like so:

  SELECT count(a.id) AS total_records, a.disregard_inventory, a.qty
      FROM artikelstammdaten a
      ...
  GROUP BY a.style
  HAVING sum(a.qty) != 0 OR (a.disregard_inventory = 1)

This works ok and selects all products which are not sold out (sum > 0 across all eans)/always available

I now want to add a function, so users can search for products that have at least 1pc in each size. In this case, style 123

   123   S   1
   123   M   0
   123   L   12
   123   XL  9

would not be included in the resultset as size M is sold out.

However I can't get it to work. This is what I have (produces rubbish):

  GROUP BY a.style
  <cfif form.select_type EQ "running_sizes">
  HAVING a.qty!= 0 OR ( a.disregard_inventory = 1 )
  <cfelse>
  HAVING sum(a.bestand) != 0 OR (a.disregard_inventory = 1) 
  </cfif>

Question:
Is it at all possible to group by style and only include style when each underlying ean has a quantity > 0? If so, thanks for pointers!

EDIT:
here is my full query, which I'm testing with:

   SELECT count(a.id) AS gesamt_datensaetze, a.nos, a.nos_anzeige, a.bestand, SUM(a.bestand>0) AS what
       FROM artikelstammdaten a

        WHERE a.aktiv = "ja"
        AND a.firma LIKE "some_company" 

        // groups by seller_id, style
        GROUP BY a.iln, a.artikelnummer
        HAVING sum(a.bestand) != 0 OR (a.nos = "ja" AND a.nos_anzeige = "ja")   
        AND ( SUM(a.bestand > 0) = COUNT(*))

Solution:
Partenthesis mising:

HAVING (( sum(a.bestand) != 0 ) OR (a.nos = "ja" AND a.nos_anzeige = "ja" ))
AND  ( SUM(a.bestand > 0) = gesamt_datensaetze  )

This works.

score 1 · Answer 1 · answered Aug 27 '12 at 20:10

You can accomplish this using a join on a subquery. Basically, join on the set of product IDs where the quantity available is zero, and then only return results where there was no match.

SELECT count(a.id) AS total_records, a.disregard_inventory, a.qty
FROM artikelstammdaten a

LEFT JOIN (
    SELECT DISTINCT id
    FROM artikelstammdaten
    WHERE qty = 0
) b
ON b.id = a.id

WHERE b.id IS NULL
...

GROUP BY a.style
HAVING sum(a.qty) != 0 OR (a.disregard_inventory = 1)

hm. I have to try again. Results are not correct and 27386 exection time :-) — frequent, Aug 27 '12 at 20:26

Jocelyn · Accepted Answer · 2012-08-27T20:38:24.113

1

I suggest the following query:

SELECT COUNT(a.id) AS total_records, a.disregard_inventory, a.qty
    FROM artikelstammdaten a
    ...
GROUP BY a.style
HAVING (SUM(a.qty) != 0 OR (a.disregard_inventory = 1))
       AND (SUM(qty>0) = total_records)

The last condition I added to the query enables to return a style only if the number of sizes for this product (total_records) is equal to the number of available sizes for this product (SUM(qty>0)).
qty>0 will either return 0 (when the product is not available in the given size, or 1 (when it is available). So SUM(qty>0) will return an integer number between 0 and the total number of sizes.

edited Aug 27 '12 at 20:38

answered Aug 27 '12 at 20:14

Jocelyn

11,209
10
43
60

still wondering what to make of the results. Give me a minute – frequent Aug 27 '12 at 20:30
nope. I'm getting a lot of results with `quantity=0` across all EANs. – frequent Aug 27 '12 at 20:33
What is the full query? What do you replace `...` with? (just before `GROUP BY`) – Jocelyn Aug 27 '12 at 20:40
I have removed everything else from the query. Pasting above. The problem is (I believe), that `SUM(a.qty>0)` is not getting all size with qty>0, but just making the sum across all sizes, so `2,0,0,0` will give `sum(a.qty>0)=2` (2+0+0+0) vs. should be 1 – frequent Aug 27 '12 at 20:44
1

No, as I explained in my answer: `SUM(a.qty>0)` will be converted to: (2>0)+(0>0)+(0>0)+(0>0), and the result would be 1, while total_records would be here equal to 4. – Jocelyn Aug 27 '12 at 20:46
that's what I hoped for, too :-) – frequent Aug 27 '12 at 20:47
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/15869/discussion-between-jocelyn-and-frequent) – Jocelyn Aug 27 '12 at 20:49

score 1 · Answer 3 · answered Aug 27 '12 at 20:14

1

I think you should check MIN(a.qty):

select product from t group by product having min(qty)>0

answered Aug 27 '12 at 20:14

valex

23,966
7
43
60

tried this, too. Also not working. Playing around with it some more. – frequent Aug 27 '12 at 20:36

How to select grouped records only if every record in a group meets a certain criteria in MySQL?

3 Answers3

Linked