sql join with multiple conditions on columns-CodePudding

I don't think I can achieve this with unions. For example, I want all cars where the key-column is "brand" and value-column is "Burago". On top of that, the key-column price with values between 100 and 220.

Should I do subqueries or something? What if I want to query the key "type" with value "sport" too? The query only returns something when I use one where clause and returns nothing when use the following query.

select `posts`.`title` from `posts`
  inner join `meta` on `posts`.`id` = `meta`.`metable_id`
  where `meta`.`key` = "price" and `meta`.`value` between 100 and 220
   and `meta`.`key` = "brand" and `meta`.`value` = "Burago"
 group by `posts`.`id`

The meta table:

ID  | post_id |   key     |       value
----------------------------------------------------
69  |   8     |   brand   | some-brand
----------------------------------------------------
70  |   8     |   type    | sport
----------------------------------------------------
70  |   8     |   price   | 100
----------------------------------------------------
71  |   8     |   brand   | some-other-brand
----------------------------------------------------
70  |   8     |   type    | coupe
----------------------------------------------------
72  |   8     |   price   | 150
----------------------------------------------------
73  |   8     |   brand   | some-brand
----------------------------------------------------
70  |   8     |   type    | cabrio
----------------------------------------------------
74  |   8     |   price   | 100
----------------------------------------------------
75  |   8     |   brand   | some-brand
----------------------------------------------------
70  |   8     |   type    | sport
----------------------------------------------------
76  |   8     |   price   | 250

CodePudding user response：

select `posts`.`title` from `posts`
  inner join `meta` on `posts`.`id` = `meta`.`metable_id`
  where `meta`.`key` = "price" and `meta`.`value` between 100 and 220
   and `meta`.`key` = "brand" and `meta`.`value` = "Burago"
 group by `posts`.`id`
having COUNT(DISTINCT `meta`.`key`) = 2