MySQL Search Join Query

I have a multiple input search form, that has 2 text boxes. One text box for "searchWords" and the other for "specificPeople".

In the first text box, you may have "Dorchester Hotel London", and in the second, you may have "Brad Pitt/Angelina Jolie". Using ASP, I convert the second text box value to a format that my IN clause will accept, such as this ('Brad Pitt','Angelina Jolie').

SELECT photoSearch.photoID, Left(photoSearch.caption,25), photoSearch.allPeople, photoSearch.allKeywords
FROM photoSearch
JOIN ( photoPeople INNER JOIN people ON photoPeople.peopleID = people.peopleID)
ON photoSearch.photoID = photoPeople.photoID AND people.people IN ('开发者_Python百科Brad Pitt','Angelina Jolie')
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

This works without errors but it's not producing records that have Brad and Angelina together. It shows records of Brad alone and records of Angelina alone. So this is where I first realised that an IN clause works like an OR.

How is it possible to amend this query, to return rows that have both of these specific names, rather than either of them?

My DB looks similar to 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

Any help gratefully received... as always :)

---

An example of what is inside the tables:

photoSearch
photoID     |       caption         |           dateCreated
1900                Dorchester Hotel...         2011-10-03

'photoPeople'
[photoID]       |   [peopleID]
1900                147
1900                148

'people'
[peopleID]      |   [people]
147                 Brad Pitt
148                 Angelina Jolie

---

Join to the photoPeople and people tables n times, where n is the number of people you are searching for:

SELECT photoSearch.photoID, Left(photoSearch.caption,25), photoSearch.allPeople, photoSearch.allKeywords 
FROM photoSearch 
JOIN ( photoPeople AS pp1 JOIN people AS p1 ON pp1.peopleID = p1.peopleID) 
ON photoSearch.photoID = pp1.photoID AND p1.people = 'Brad Pitt'
JOIN ( photoPeople AS pp2 JOIN people AS p2 ON pp2.peopleID = p2.peopleID) 
ON photoSearch.photoID = pp2.photoID AND p2.people = 'Angelina Jolie'
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 

---

The problem is that the IN clause

AND people.people IN ('Brad Pitt','Angelina Jolie')

looks for exact matches, so the query is doing exactly what you are asking. If you had done

AND (people.people LIKE '%Brad Pitt%' OR people.people LIKE '%Angelina Jolie%') 

you would get the results you want. If you want, you could use

AND instr('/Brad Pitt/Angel Jolie/','/'+people.people+'/') > 0

you can get the results you expect..

Try this:

SELECT photoSearch.photoID, Left(photoSearch.caption,25), photoSearch.allPeople,        photoSearch.allKeywords
    FROM photoSearch ps
    JOIN photoPeople pp on pp.photoId=ps.photoId
    JOIN people on people.peopleId = pp.PeopleId
    WHERE MATCH (caption, allPeople, allKeywords) AGAINST ('+dorchester +hotel' IN BOOLEAN MODE)
    AND photoSearch.dateCreated BETWEEN '2011-07-21' AND '2011-10-23'
    AND people.people IN ('Brad Pitt','Angelina Jolie')
    ORDER BY photoSearch.dateCreated