Querying items where ALL values match in a single column in MYSQL

Question

I'm trying to get the results of a table where ALL the items match AND then only show the results where they all match within a specific stockId.

I've tried to do variantId IN (4,1,8) as an example, but it shows me all the results not where they ALL match 4 1 AND 8:

SELECT * FROM `products_bind_variant_stock_combination` 
WHERE `variantId` IN (4,1,8)

I'm expecting it to return either 3 results of where the variant Id match 4, 1 AND 8. Or I want to show just the stock ID where the IDs match all of those.

My structure in

products_bind_variant_stock_combination


|subId        |int(10)|No |
|productsId   |int(10)|Yes|NULL
|stockId      |int(10)|Yes|NULL
|variantId    |int(10)|No |

A small sample is:

|1|69|1|4
|2|69|1|1
|3|69|1|8
|4|69|2|5
|5|69|2|1
|6|69|2|8
|7|69|3|6
|8|69|3|1
|9|69|3|8

The result I want when searching variantId as matching 4,1,8 is:

|1|69|1|4
|2|69|1|1
|3|69|1|8

Do you want a product with variantId values 1, 4, 8, 5 to be returned? Add that combination to your sample table data, and perhaps adjust the expected result. — jarlh, Dec 20 '18 at 09:40

score 2 · Accepted Answer · answered Dec 20 '18 at 09:18

In a Derived Table, we can get all the stockId values which has only 1,4,8 variantId values. To find that, we can GROUP BY on stockId and using conditional aggregation based filtering in HAVING clause.

Now, we can Join back to the main table using stockId and get all the rows for that stockId value.

SELECT
  t1.*
FROM products_bind_variant_stock_combination AS t1
JOIN (SELECT stockID
      FROM products_bind_variant_stock_combination
      GROUP BY stockID 
      HAVING SUM(variantId = 1) AND /* has variantId = 1 */
             SUM(variantId = 4) AND /* has variantId = 4 */
             SUM(variantId = 8) AND /* has variantId = 8 */
             NOT SUM(variantId NOT IN (1,4,8)) /* no other variantId exists */
     ) AS t2 ON t2.stockId = t1.stockID

yip102011 · Answer 2 · 2018-12-20T09:57:36.223

2

use group by and group_concat to find out the stockId list first, then filter the table with the stockId list.

updated, solve ordering problem.

select * from `products_bind_variant_stock_combination`
where stockId in (select stockId 
                    from `products_bind_variant_stock_combination` 
                    group by stockId 
                    having group_concat(variantId order by stockId) = '1,4,8')

edited Dec 20 '18 at 09:57

answered Dec 20 '18 at 09:26

yip102011

751
4
11

Thanks for this. Works great and is a simpler solution than I expected. – dave sanchez Dec 20 '18 at 09:41
'4, 1, 8', what happens if you get '1, 4, 8', or '1, 8 ,4' etc – jarlh Dec 20 '18 at 09:41
That's a good point actually, just realized the order can be different, though the query is nice and short. – dave sanchez Dec 20 '18 at 09:43
@davesanchez for production data, I would not recommend this query. Because their may be a scenario where a `stockId` can have a large number of `variantId` values. `Group_Concat()` is limited by `group_concat_max_len` and in this case, the string generated after `Group_concat()` would be truncated and give wrong results. Also, as jarlh mentioned that `Order By` is also crucial in this query. – Madhur Bhaiya Dec 20 '18 at 09:55
@yip102011 - Very clever adding in the ordering internally. I like this solution alot too. – dave sanchez Dec 21 '18 at 09:24

score 0 · Answer 3 · answered Dec 20 '18 at 10:21

Try something like this:

select * from products_bind_variant_stock_combination p
where 
  exists (select 1 from products_bind_variant_stock_combination p2 where p2.stockId = p.stockId and  variantId=1)
  and exists (select 1 from products_bind_variant_stock_combination  p2 where p2.stockId = p.stockId and variantId=4)
  and exists (select 1 from products_bind_variant_stock_combination  p2 where p2.stockId = p.stockId  and variantId=8);

which gives you all records with the same stockId, where other records with the other variantIds exist.

Querying items where ALL values match in a single column in MYSQL

3 Answers3