title: MySQL Search Join Query
body: <p>I have a multiple input search form, that has 2 text boxes. One text box for "searchWords" and the other for "specificPeople".</p>
  
  <p>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').</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>How is it possible to amend this query, to return rows that have both of these specific names, rather than either of them?</p>
  
  <p>My DB looks similar to this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Any help gratefully received... as always :)</p>
  
  <hr>
  
  <p>An example of what is inside the tables:</p>
  
  <pre>
  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
  </pre>