MySQL Query - Join troubles... again?

Question

I'm hoping somebody will be able to help me with a query I'm stuck on.

I have multiple input search form for a photos database. One text box 'searchWords' is to search my 'photoSearch' table and the other text box 'specificPeople' searches the 'people' table, to search for specific people.

For example, a user might search for "Dorchester Hotel London" in box 1 and "Brad Pitt" in box 2.

This is what I am trying but it is only bring back results for "Dorchester Hotel London", it is ignoring my people search:

SELECT photoSearch.photoID, Left(photoSearch.caption,25), photoSearch.allPeople, photoSearch.allKeywords
FROM photoSearch
LEFT JOIN ( photoPeople INNER JOIN people ON photoPeople.peopleID = people.peopleID)
ON photoSearch.photoID = photoPeople.photoID AND people.people IN ('kate moss','jamie hince')
WHERE MATCH (caption, allPeople, allKeywords) AGAINST ('+dorchester +hotel' IN BOOLEAN MODE)
AND
photoSearch.dateCreated BETWEEN '2011-07-21' AND '2011-10-23'
ORDER BY photoSearch.dateCreated

If I take the JOIN away, the fulltext search is perfect. My table schema is a little like this:

**photoSearch**
photoID     INT / AUTO / INDEX
caption     VARCHAR(2500) /  FULLTEXT
allPeople   VARCHAR(300) / FULLTEXT
allKeywords VARCHAR(300) / FULLTEXT
dateCreated DATETIME / INDEX

**photoPeople**
photoID     INT / INDEX
peopleID    INT / INDEX

**people**
peopleID    INT / INDEX
people      VARCHAR(100) / INDEX

Is this fixable or am I totally doing the wrong thing? If so, could somebody show me what I'm meant to be doing :)

Answer 1

You are doing a LEFT JOIN which means it will return results for the left table (photoSearch) even if the join condition fails,

Change LEFT JOIN to JOIN.