I've got a multiple input search form, which has 2 text boxes. One text box for "searchWords" and also the other for "specificPeople".

Within the first text box, you might have "Dorchester Hotel London", as well as in the 2nd, you might have "Kaira Pitt/Julia RobertsInch. Using ASP, I convert the 2nd text box value to some format that my IN clause need, similar to 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 ('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 is not creating records which have Kaira and Angelina together. It shows records of Kaira alone and records of Angelina alone. Making this where When i first realized that the IN clause works as an OR.

How can you really amend this question, to come back rows which have these two specific names, instead of either of these?

My DB looks such as 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... of course :)


A good example of what's within the tables:


photoSearch

photoID            caption                    dateCreated

1900                Dorchester Hotel...         2011-10-03

'photoPeople'

[photoID]          [peopleID]

1900                147

1900                148

'people'

[peopleID]         [people]

147                 Kaira Pitt

148                 Julia Roberts

Join towards the photoPeople and people tables n occasions, where n is the amount of people you are looking 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 

However , the IN clause

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

searches for exact matches, therefore the totally doing exactly what you're asking. Should you have had done

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

you can get the outcomes you would like. If you would like, you could utilize

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

you will get the outcomes you anticipate..

Do 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