multiple conditions within left join statement

Hey Guys,

Issue is as follows:

1) I've got two tables in my database - one is called 'items', the second one is 'keywords'.
2) In table 'keywords' there's a field called 'item_id' which will connect the two tables.


items
id
name

keywords
value
item_id


What I want to do is run a query that will select items and its attached keywords. Basically the following query will do the job:

SELECT name FROM items LEFT JOIN keywords ON items.identifier = keywords.item_id

Problem is that this query will return a record for every unique keyword.value. This is relevant since I want multiple keywords to be attached to a single item.

Now that I like to select items limited by multiple conditions I won't succeed. For example:

item1 has 2 keyword values: 'green' and 'red'.
When i try:

SELECT name FROM items LEFT JOIN keywords ON items.identifier = keywords.item_id WHERE keywords.value = 'green' AND keywords.value = 'red'

Nothing's returned. Could anybody tell me how to solve this?
TIA

That second query fails because it's impossible for keywords.value to be equal to two different strings at the same time. Try WHERE keywords.value = 'green' OR keywords.value = 'red' instead.

I know that, but in this case both 'green' and 'red' are attached to this item. My aim is to return that item while both colors are used as filter.

Do I need subselects or other kind of joins...?