title: MySQL/ASP - Confusing SQL Query
body: <p>I have a celebrity photo website, 100,000 photos and growing. The user has made a search "Oscar Awards Ceremony" and I currently loop through the results to display the relevant photos.</p>
  
  <p>Now, I'd like to offer a search refinement column, that sits beside the results, that will show specific names of the people that were photographed at that particular event (based on photoID's from results), showing the quantity of photos next each name and displaying the names in order of highest quantity.</p>
  
  <p>Like so:</p>
  
  <hr>
  
  <p><strong>You searched for:</strong> "Oscar Awards Ceremony" (12,489)</p>
  
  <p><strong>Specific People:</strong></p>
  
  <p>Brad Pitt (1,726)</p>
  
  <p>Angelina Jolie (1,302)</p>
  
  <p>Edward Norton (929)...</p>
  
  <hr>
  
  <p>This is my database schema:</p>
  
  <pre>
  photoSearch (tbl)
  photoID             INT(11)         Index       Auto-Increment
  headline            VarChar(1000)   FullText
  caption             VarChar(2500)   FullText
  dateCreated         DateTime        Index
  
  photoPeople (tbl)
  photoID             INT(11)
  peopleID            INT(11)
  
  people (tbl)
  peopleID            INT(11)         Primary     Auto-Increment
  people              VarChar(255)    Index
  </pre>
  
  <p>So basically, I have a result set of photoID's from the users last search. Using that result set, I need to query the 'photoPeople' and 'people' table, to list the people that are related to those photoID's, I need to show the quantity and to order them by quantity.</p>
  
  <p>How can I go about doing this? Can it be done in one query? Will I have to write the results in to a table to be able to count and order them?</p>
  
  <p>This is what I have done so far but only lists peoples names:</p>
  
  <pre>
  SELECT * FROM people
  INNER JOIN photopeople ON photoPeople.peopleID = people.PeopleID
  WHERE photoID IN ('87345','8900','83458','63746')
  </pre>
  
  <p>I'm not looking for exact code, although it would be very helpful, I'm just looking for advice on how to accomplish this correctly with performance in-mind. I think my brain is capable of showing the relevant people, but not in any order and certainly without a count!</p>
  
  <p>Any help gratefully received...</p>
  
  <p>P.S. When I get a working solution together, I will apply this to my events table and keywords too. This means if somebody searched "Brad Pitt", the event "Oscar Awards Ceremony" will appear in the 'refine search' column, with 1,726 next to the event.  </p>