当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Select involving Many-to-many table

问题描述:

I have 3 tables: products, features and linking table (many-to-many)

For example, I need to get from linking table all products, which have 3 features with id = 1, 2 and 3

 link_id | product_id | feature_id

---------------------------------

100 | 1 | 1

101 | 1 | 2

102 | 1 | 3

Guys, what's the simplest way to write query?

SELECT pr.* FROM product pr, link lk WHERE pr.id = lk.product_id

AND lk.feature_id = 1 AND lk.feature_id = 2 AND lk.feature_id = 3

Doesn't work

网友答案:

As you are checking the values for three different records, you can't check them all on all records.

Make three joins, one agains a link record for each feature:

select
  pr.*
from
  product pr
  inner join link lk1 on pr.id = lk1.product_id and lk1.feature_id = 1
  inner join link lk2 on pr.id = lk2.product_id and lk2.feature_id = 2
  inner join link lk3 on pr.id = lk3.product_id and lk3.feature_id = 3
网友答案:

Do it like this:

SELECT pr.* FROM product pr, link lk 
WHERE pr.id = lk.product_id 
AND lk.feature_id IN ( 1, 2, 3)
with ur; 
分享给朋友:
您可能感兴趣的文章:
随机阅读: