MySQL努力寻求查询以查找产品之间的类似细节

Question

I have a query to write and I am absolutely stumped on how to do it. Here's my situation, I am trying to provide a particular product_ID, then match all of the other product_IDs in the database that have at least the same intDescription_detail_IDs as the provided product_ID.

The relevant tables look like this:

tblproducts

=========================

product_ID | product_name

=========================

| 1    |    dresser  |
| 2    |    bookcase | 
| 3    |    table    |
| 4    |    chair    |

=========================

tbldescriptions

=========================================================================

|description_ID| intDescription_product_ID | intDescription_detail_ID   |

=========================================================================

|   1      |        1      |        1       |
|   2      |        1      |        2       |
|   4      |        2      |        1       |
|   5      |        2      |        2       |
|   6      |        2      |        6       |
|   7      |        3      |        1       |
|   8      |        3      |        3       |
|   9      |        3      |        4       |
|   10     |        4      |        1       |
|   11     |        4      |        2       |
|   12     |        4      |        7       |

As an example, if I provided the product_ID "1", then I would like to return all of the product_IDs that at least have intDescription_detail_ID 1 and 2.

So, the product_IDs that should be returned are 1, 2, and 4, because all of these products have the intDescription_detail_ID of 1 and 2 among their details.

I am highly confused about how to write a query like this, so any help is greatly appreciated!

Answer 1

Alternative to @Strawberry’s suggestion with JOINs this can also be done using HAVING for filtering products that have (at least) the same number of rows with the same intDescription_detail_IDs as the product the search is done for:

SELECT intDescription_product_ID
FROM tbldescriptions t1
WHERE intDescription_detail_ID IN (
  SELECT intDescription_detail_ID
  FROM tbldescriptions t2
  WHERE t2.intDescription_product_ID = 1
  )
GROUP BY intDescription_product_ID
HAVING count(*) >= (
  SELECT count(intDescription_detail_ID)
  FROM tbldescriptions t3
  WHERE t3.intDescription_product_ID = 1
  )

http://sqlfiddle.com/#!2/ce698/2

One should keep in mind though that HAVING is applied last, so that will select all products with at least one matching intDescription_detail_ID first, and filter the results based on the actual count afterwards – so depending on the size and characteristic of your data set that might not be the best performing solution.