Using JOIN in a WHERE clause with multiple joined rows

[Jesper]

Hi everyone,

 

One sentence was not enough to explain the problem so the title might not have made you fully understand the issue.

 

I am currently building an application, for which I will now describe the table structure (simplified).

 

TABLE posts

+---+----------+

| ID |    title  |

+---+----------+

|  1 |  Post 1  |

|  2 |  Post 2  |

|  3 |  Post 3  |

|  4 |  Post 4  |

+---+----------+

 

TABLE post_options

+---+------------+------------+------------+

| ID |  post_id  |    key      |    value    |

+---+------------+------------+------------+

|  1 |      2      |  brand    |    Sony    |

|  2 |      2      |    price    |      20      |

|  3 |      1      |    price    |      60      |

|  4 |      3      |    price    |      10      |

|  5 |      3      |    brand  |    ABC    |

+---+------------+------------+------------+

 

Now what I want is to add a search option to my website: people have to be able to search for posts where (for example) the brand is Sony and the price is 20. It is possible that more rows match, by the way.

 

What I tried was to JOIN post_options on posts, like so:

JOIN
	post_options
ON
	posts.ID=post_options.post_id

 

That went fine, so I wanted to search that (brand Sony, price 20), for which I tried:

SELECT
	ID,
	title
FROM
	posts
JOIN
	post_options
ON	
	posts.ID=post_options.post_id
WHERE
	post_options.key='brand'
AND	post_options.value='Sony'
AND	post_options.key='price'
AND	post_options.value='20'

 

Obviously, that didn't work, as MySQL doesn't know of course which key belongs to which value. The question is now: How to do this rightly?

 

How to return the posts for which there is one post option key 'brand' with value 'Sony' and one key 'price' with value '20', with matching post_id's?

 

 

Thanks in advance!

 

MySQL Version: 5.0.90-community

[Jesper]

Solved using the AND operator and marking each joined row as a separate one:

JOIN table1 key1 ON table2.ID = key1.post_id AND key1.key = 'price' and key1.value = '20'

And so on :-).