title: Using joins rather than subqueries
body: <p>I have a mysql database containing two tables - <strong>[PM_text]</strong> and <strong>[PM_bact]</strong>, among others. The two tables contain one common data column. My intention is to retrieve information from the first table using matches to the common data column from the second table. I initially used a nested query for this and it had worked okay. I suddenly noticed that it does not execute conclusively any more. I want to use 'joins' instead, to see if that would optimize it further.</p>
  
  <p>This is my initial query: </p>
  
  <p><strong>SELECT pmid, year, title, pages FROM PM_text WHERE pmid IN (SELECT pmd_id from PM_bact WHERE bact1 LIKE ? AND bact2 LIKE ? AND associat LIKE ?);</strong></p>
  
  <p>But I intend to change that to:</p>
  
  <p><strong>SELECT DISTINCT PM_text.pmid, PM_text.year, PM_text.title, PM_text.pages FROM PM_text, PM_bact WHERE PM_text.pmid = PM_bact.pmd_id AND PM_bact.bact1 LIKE ? AND PM_bact.bact2 LIKE ? AND PM_bact.associat LIKE ? ;</strong></p>
  
  <p>I need to ensure matches to 'bact1', 'bact2' and 'associat' in the second table and am concerned that the repeated use of 'AND' in the query might affect its optimization.   </p>
  
  <p>I am sure that there are better ways of doing this and being new to the subject I would appreciate any help you can render. Thanks a lot. </p>