How do I self join on multiple values in mysql?

Specifically, I want to query word press posts based on N number of meta values which are in a key/value type of table.

I simply can't find a good example of a working query.

In plain English, the query would be like this.

Select all posts that city=Dallas, style=ranch, price between 100k and200k, po开发者_JAVA技巧ol=true

I may have more or less meta values I need to compare against.

For non-word press users, the meta values are in one table with each meta associated with a post Id from the posts table.

---

Ah, the joys of an EAV. In short, you need to do multiple subqueries or a crosstab.

Select ...
From Posts
Where post_id In    (
                    Select post_id
                    From Meta
                    Where Attribute = 'City'
                        And Value = 'Dallas'
                    )
    And post_id In  (
                    Select post_id
                    From Meta
                    Where Attribute = 'Style'
                        And Value = 'Ranch'
                    )
    And post_id In  (
                    Select post_id
                    From Meta
                    Where Attribute = 'Price'
                        And Cast(Value As int) Between 100000 And 200000
                    )
    And post_id In  (
                    Select post_id
                    From Meta
                    Where Attribute = 'Pool'
                        And Value = 'True'
                    )   

Here is another form which builds a crosstab. It is more compact but may not perform as well:

Select ...
From Posts As P
    Join    (
            Select post_id
                , Min( Case When Attribute = 'City' Then Value End ) As City
                , Min( Case When Attribute = 'Style' Then Value End ) As Style
                , Min( Case When Attribute = 'Price' Then Cast(Value As int) End ) As Price
                , Min( Case When Attribute = 'Pool' Then Value End ) As Pool
            From Meta
            Where Attribute In('City','Style','Price','Pool')
            Group By post_id
            ) As Attributes
        On Attributes.post_id = P.post_id
Where Attributes.City = 'Dallas'
    And Attributes.Style = 'Ranch'
    And Attributes.Price Between 100000 And 200000
    And Attributes.Pool = 'True'