How to find records with appropriate parameters?

Good afternoon.
There are subscriptions to the filter.

Table subscriptions subscriptions: id, email
1, test@ururu.ru

Table with possible filters subscriptions_filters: id, code, name
1, brand, Brand car
2. model, Model
3. power_from, Power from

Table subscriptions_filters_values: id, subscription_id, filter_id, value
1, 1, 1, BMW
2, 1, 2, M5

If I had to find all subscriptions which car brand BMW model M5 and a capacity of 155, the query would be simple.

SELECT * FROM subscriptions as sb
LEFT JOIN subscriptions_filters_values as sfv ON sfv.subscription_id = sb.id
LEFT JOIN subscriptions_filters as sf ON sf.id = sfv.filter_id
WHERE (sf.code = 'brand' and sfv.value = 'BMW') AND (sf.code = 'model' and sfv.value = 'M5') AND (sf.code = 'power' and sfv.value = '155')


What to do if I need to find all subscriptions BMW M5 155 HP, but those who have some of the parameters not set? For example subscribe to a filter which lists only BMW's without model and capacity.

UPD:
And even though the above query won't work because is not correct. You have the options? Do not know how to solve, but to select all filters from the database and in PHP to compare...
March 19th 20 at 08:52
1 answer
March 19th 20 at 08:54
Solution
The query you have is not correct because where protivorechie - like sf.code can be the same brand and model, etc. in one record?
To make it correct you need to put between brackets OR, not and.
For the incomplete condition just remove from a query to the bracket with the corresponding conditions.
If you put OR, it will find all subscriptions that have at least one value matches, and what would have matched all the settings from the subscription.
Can be subscription, which shows only the BMW and there are subscriptions, which specify and BMW and M5, and 155 HP, their I need to - mike26 commented on March 19th 20 at 08:57
@mike26Slightly misunderstood the entire neglect of the situation.
OR Express, because each filter you have in a single record, and therefore not suitable in any case.
Next you need to group the sample and count the number of included filters, where the output number of filters = 3. - norval_Larson66 commented on March 19th 20 at 09:00
Group, probably sb.id and count the number of sf.id. - norval_Larson66 commented on March 19th 20 at 09:03
@norval_Larson66, thank you! Good decision. Though in reality it is still difficult, but it seems solved. I will try ))) have a day with it I suffer... If that doesn't work, compare with PHP and figs with him, gonna lose my mind ) - mike26 commented on March 19th 20 at 09:06

Find more questions by tags SQLPostgreSQLMySQL