...

View Full Version : multiple conditions within left join statement



woodwood
05-16-2011, 02:27 PM
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

Fumigator
05-16-2011, 03:33 PM
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.

woodwood
05-19-2011, 09:56 PM
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...?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum