Estimated number of products in two categories

[Jolied]

Table product_filter There are two pillars: product_id and filter_id♪ We need a number. product_idfor which there's a record filter_id = 1 and filter_id = 5♪
How do you do that?

[Demir]

In the first request, the lines covered by the condition shall be removed. and lines f=1 and lines f=5)

The second request is to calculate the number of lines of the requested request (the requested is identical to the first).

http://sqlfiddle.com/#!9/876f0/1

MySQL 5.6 Schema Setup:

create table t (p int, f int);
insert into t values

(1, 1)

,(2, 1)

,(1, 5)

,(3, 1)

,(4, 5)

,(1, 1)

,(2, 5)

;

Query 1:

select *

from t as t1

join t as t2

on t1.p = t2.p

where t1.f = 1 and t2.f = 5

group by t1.p

http://sqlfiddle.com/#!9/876f0/1/0 :

| p | f | p | f |

|---|---|---|---|

| 1 | 1 | 1 | 5 |

| 2 | 1 | 2 | 5 |

Query 2:

select count() from

(

select t1.p

from t as t1

join t as t2

on t1.p = t2.p

where t1.f = 1 and t2.f = 5

group by t1.p

) as t0

http://sqlfiddle.com/#!9/876f0/1/1 :

| count() |

|----------|

|        2 |