mySQL query - I have a table 'database'.'table_product_options' containing around 1000 rows. This table should contain 4x rows for each 'product_id' - the same product assigned to each 'customer_group' (illustrated below). This should equate to ~250 rows per customer_group.
However, I have run a COUNT on each 'customer_group', and found that there are a handful of missing entries for groups a+ c (250 rows total each for groups b+ d, but only 245 rows for groups a+ c).
+----------------+----------------+
| product_id | customer_group |
+----------------+----------------+
| 1 | a | }
| 1 | b | }
| 1 | c | }-each 'product_id' has 4x 'customer_group' attached
| 1 | d | }
| 2 | a |
| 2 | b |
| 2 | c | << 1 missing entry (customer_group = d)
| 3 | a | << 3 missing entries (customer_group = b, c, d)
+----------------+----------------+
How can I run a SELECT query (or alternative) to display which 'product_id' has less than 4 rows assigned? Or, in other words, how can I find the missing entries?
I have tried many different queries including SELECTs combined with WHERE NOT EXIST and etc but I am running out of ideas. I have modified a dozen stackoverflow answers but none are quite suitable, and haven't been able to modify the syntax to work.
Any help would be greatly appreciated (as I anticipate the same problem cropping up again in the future..). I may end up scouring the table by eye!
select product_id, count(customer_group)
from table_product_options
group by product_id
having count(customer_group) < 4
You might have to switch the having and group by line. I get the order confused.