如何从匹配对的MySQL表中找到共享键值？ [关闭]

Question

I have a two column MySQL table which represents the variations of a product with three different options (size, colour etc.)

##############################
#  VariationID  #  OptionID  #
##############################
#            1  #        10  #
#            1  #        20  #
#            1  #        30  #
#            2  #        11  #
#            2  #        20  #
#            2  #        30  #
#            3  #        10  #
#            3  #        22  #
#            3  #        30  #
#            4  #        10  #
#            4  #        20  #
#            4  #        33  #
##############################

The table is generated by the PHP framework I'm using (Silverstripe) so I can't easily modify it.

How do I identify which VariationID is matched to specific options?

Can I run a MySQL query that returns the VariationID for the variation with the OptionIDs 10,20,30?

If it isn't possible in raw MySQL what's the best approach in PHP?

Answer 1

You can use SQL directly, but it is a bit messy:

SELECT 
  A.VariationID AS VariationID
FROM
  tablename AS A
  INNER JOIN tablename AS B ON A.VariationID=B.VariationID
  INNER JOIN tablename AS C ON A.VariationID=C.VariationID
WHERE
  A.OptionID=10
  AND B.OptionID=20
  AND C.OptionID=30

This will find the VariationID of rows, that exist for OptionID values of 10, 20 and 30