How to make such sql query from two pages?

Question

MySQL

There are two tables, one list with parameters, the second list with values

​​You need to make a selection by the filter name = the value of the
filters can be several
Compiled such a query

SELECT P.post FROM fields_post AS P, fields AS F
WHERE (P.value = 'Medium' AND F.name = 'Size')
AND (P.value = 'Purebred' AND F.name = 'Type')
AND P.field = F.id

But it doesn't work with multiple filters.
How to rewrite it so that the selection works?

Answer 1

It's not entirely clear what exactly you are doing, but perhaps you wanted something like this:

SELECT P.post FROM fields_post AS P, fields AS F
WHERE P.field = F.id
AND (
  (P.value = 'Medium' AND F.name = 'Size')
  OR -- это важно
  (P.value = 'Purebred' AND F.name = 'Type')
)

Or how do you want it to work if you have P.value = 'Medium' and P.value = 'Purebred' at the same time?

Answer 2

1) Personal feeling: please do not join the table in where. If you join them, write a normal join (like t1 join t2 on t1.col = t2.col). Practice shows that those who match tables in where do not understand what they are doing.
2) Information is not enough. Are you sure you need to join by P.field = F.id? You need a normal description of what the tables are.
3) Several conditions - what is this? And for the first one to come out (record(s) 'Medium' + 'Size') and the second one (record(s) 'Purebred' + 'Type') at the same time? Then ((P.value = 'Medium' AND F.name = 'Size')
or (P.value = 'Purebred' AND F.name = 'Type'))