title: Multiple joins on same table and column using multiple aliases
body: <p>I have a celebrity photo website. It has 1 table that holds all searchable data for each photo called <code>photoSearch</code> and the full set of data is spread over many tables (normalization), named <code>photos</code>, <code>photoPeople</code> and <code>people</code>.</p>
  
  <p>Table <code>photoPeople</code> contains photoID's and peopleID's and the <code>people</code> table contains peopleID's and the people's names. The <code>photos</code> table contains captions, headlines, heights, widths, file sizes, sales codes etc etc.</p>
  
  <p>This is my current script which produces good results.</p>
  
  <pre><code>SELECT 
       p.photoID, 
       p.setID, 
       p.headline, 
       p.caption,
       GROUP_CONCAT(pe.people ORDER BY pe.people ASC SEPARATOR ';') AS people
  FROM 
       photos p
  INNER JOIN
       (
          SELECT photoID 
          FROM photoSearch 
          WHERE MATCH (allKeywords, shortCaption)
          AGAINST ('+red +dress +claridges +hotel' IN BOOLEAN MODE) 
          LIMIT 50
       ) pids ON p.photoID = pids.photoID
  ***********
  LEFT JOIN
       photoPeople pp ON p.photoID = pp.photoID
  LEFT JOIN
       people pe ON pp.peopleID = pe.peopleID
  ***********
  GROUP BY p.photoID
  </code></pre>
  
  <p>The problem is, instead of just selecting celebrity names to display in the results, I now want to search for one or more celebrity names to alter the results.</p>
  
  <p>I'm pretty sure it's not possible to add two or more where clauses on the same column (where pe.people = 'angelina jolie' and pe.people = 'brad pitt'), so I'm pretty sure it needs to be done using JOINS.</p>
  
  <p>This confused me a bit. So I asked a question here on SO to find out how I could achieve this and somebody advised this:</p>
  
  <blockquote>
    <p>"Join to the 'photoPeople' and 'people' tables <strong><em>n</em></strong> times, where <strong><em>n</em></strong> is the
    number of people you are searching for."</p>
  </blockquote>
  
  <p>I tried many different ways of achieving this but so far have failed. I know what he said involves removing the joins in between the two lines of asterisks (code above) and replacing them with <strong><em>n</em></strong> amount of joins to the people table, but I just can't figure it out.</p>
  
  <p>I tried this but phpMyAdmin has now been thinking for the last 10 minutes!:</p>
  
  <pre><code>********
  JOIN ( photoPeople AS pp1 JOIN people AS p1 ON pp1.peopleID = p1.peopleID) 
  ON p1.people = 'Brad Pitt'
  JOIN ( photoPeople AS pp2 JOIN people AS p2 ON pp2.peopleID = p2.peopleID) 
  ON p2.people = 'Angelina Jolie'
  ********
  </code></pre>
  
  <p>On top of the above attempt taking FOREVER, I also struggle to see how I can alter the GROUP_CONCAT function to get the results from more than one join/alias (p1, p2, p3 etc)!</p>
  
  <p>Please, I would love some advice to get this working correctly.</p>
  
  <hr>
  
  <p><strong>TEST SCENARIO</strong></p>
  
  <p>If I run my query as-is, this is what I would see:</p>
  
  <pre><code>Photo:    120030
  Set:      8803
  Headline: Brad and Angelina arrive at film premiere.
  People:   Brad Pitt; Angelina Jolie
  
  Photo:    120031
  Set:      8803
  Headline: Brad and Angelina arrive at film premiere.
  People:   Angelina Jolie
  
  Photo:    120032
  Set:      8803
  Headline: Brad and Angelina arrive at film premiere.
  People:   Brad Pitt
  </code></pre>
  
  <p>I want my new query to search for names, so if I search for Angeline Jolie and Brad Pitt, I will see only this:</p>
  
  <pre><code>Photo:    120030
  Set:      8803
  Headline: Brad and Angelina arrive at film premiere.
  People:   Brad Pitt; Angelina Jolie
  </code></pre>
  
  <hr>
  
  <p><strong>NEW ATTEMPT:</strong></p>
  
  <p>No errors but returned 0 results, where there should be 1000's of results for 'Brad Pitt' and 'Angelina Jolie' in 'France'.</p>
  
  <pre><code>SELECT 
       p.photoID, 
       p.setID, 
       p.headline, 
       p.caption,
       GROUP_CONCAT(pe.people ORDER BY pe.people ASC SEPARATOR ';') AS people
  FROM 
       photos p
  INNER JOIN
       (
          SELECT photoID 
          FROM photoSearch 
          WHERE MATCH (allKeywords, shortCaption)
          AGAINST ('+France' IN BOOLEAN MODE) 
          LIMIT 50
       ) pids ON p.photoID = pids.photoID
  LEFT JOIN
       photoPeople pp ON p.photoID = pp.photoID
  JOIN
       people pe ON pp.peopleID = pe.peopleID
       AND (pe.people = 'Angelina Jolie' OR pe.people = 'Brad Pitt')
  GROUP BY p.photoID
  </code></pre>
  
  <p>If I remove the INNER JOIN section and run this, it returns all photos of Brad and Angelina whether they are together or not, which is incorrect - I just need them together. If I also remove the <code>OR pe.people = 'Brad Pitt'</code>, it returns photos of Angelina correctly, I just can't get the whole thing linked up!</p>