title: MySQL Query - Many to Many
body: <p>I need to find match for a user. </p>
  
  <p>Each user has <strong>multiple</strong> "work category"(like Accounting, Banking, Finance etc) and <strong>multiple</strong> "preferred work category"(to find matching profile in those specified category)</p>
  
  <p>What is the effective way to find the match using MySQL Query.</p>
  
  <p>Structure of the <strong>MyProfile</strong> table:</p>
  
  <pre><code>User_Id | Work_Id
  1       | 5
  1       | 7
  1       | 8
  2       | 7
  2       | 9
  </code></pre>
  
  <p>Structure of <strong>PreferedWork</strong> table</p>
  
  <pre><code>User_Id | Work_Id
  1       | 7
  1       | 9
  2       | 1
  2       | 8
  </code></pre>
  
  <p>I have to check against 100,000 profiles and return Only 10 profiles.</p>
  
  <p><strong>EDIT:</strong>
  My Original query (without join):</p>
  
  <pre><code>$getMatchesQry = "
          select ed.Entity_Id as entId,ed.Status as status,ed.First_Name,ed.Profile_Pic_Url,ed.Sex,ed.Current_Lat,ed.Current_Long,ed.community,ed.ethnicity,ed.Fb_Id,ent.Last_Active_Dt_Time,
        YEAR(NOW()) - YEAR(ed.dob) - (RIGHT(NOW(), 5) &lt; RIGHT(ed.DOB, 5)) as age, pr.Orientation
          from entity_details ed,entity ent, preferences pr
          where 
          (3959 * acos( cos( radians('" . $prefRow['Current_Lat'] . "') ) * cos( radians(ed.Current_Lat) ) * cos( radians(ed.Current_Long) - radians('" . $prefRow['Current_Long'] . "') ) + sin( radians('" . $prefRow['Current_Lat'] . "') ) * sin( radians(ed.Current_Lat) ) ) ) &lt;= " . $prefRow['Preference_radius'] . "          
          and
          (" . $pref_sex_str . ")  
          and
          (" . $prefComWord . ") and (" . $prefRelWord . ")
          and
          pr.Orientation  = '" . $prefRow['Orientation'] . "' 
          and             
          '" . $prefRow['min_height'] . "' &lt;= ed.height 
          and 
          ed.height  &lt;= '" . $prefRow['max_height'] . "'                                
          and            
          ed.Entity_Id = ent.Entity_Id and  
          ed.Entity_Id = pr.Entity_Id and              
          ent.status = 1  and  ed.Fb_Id != '" . $prefRow['Fb_Id'] . "'  and 
          ed.Entity_Id NOT IN (select Entity2_Id from likes where Entity1_Id = '" . $entityId . "' and (Like_Flag = '1' OR  Like_Flag = '2' OR  Like_Flag = '3' OR  Like_Flag = '4'))             
          having 
          age BETWEEN '" . $prefRow['Preference_lower_age'] . "' AND '" . $prefRow['Preference_upper_age'] . "' LIMIT 10";
  </code></pre>
  
  <p>when i add join, </p>
  
  <pre><code>$getMatchesQry = "
          select ed.Entity_Id as entId,ed.Status as status,ed.First_Name,ed.Profile_Pic_Url,ed.Sex,ed.Current_Lat,ed.Current_Long,ed.community,ed.ethnicity,ed.Fb_Id,ent.Last_Active_Dt_Time,
        YEAR(NOW()) - YEAR(ed.dob) - (RIGHT(NOW(), 5) &lt; RIGHT(ed.DOB, 5)) as age, pr.Orientation
          from entity_details ed,entity ent, preferences pr, **pref_occupation ul1, users_occ ul2**
          where 
          (3959 * acos( cos( radians('" . $prefRow['Current_Lat'] . "') ) * cos( radians(ed.Current_Lat) ) * cos( radians(ed.Current_Long) - radians('" . $prefRow['Current_Long'] . "') ) + sin( radians('" . $prefRow['Current_Lat'] . "') ) * sin( radians(ed.Current_Lat) ) ) ) &lt;= " . $prefRow['Preference_radius'] . "          
          and
          (" . $pref_sex_str . ")  
          and
          (" . $prefComWord . ") and (" . $prefRelWord . ")
          and
          pr.Orientation  = '" . $prefRow['Orientation'] . "' 
          and             
          '" . $prefRow['min_height'] . "' &lt;= ed.height 
          and 
          ed.height  &lt;= '" . $prefRow['max_height'] . "'                    
          and
          **( ul1.Occupation_Id = ul2.Work_Id AND ul1.Entity_Id != ul2.Entity_Id AND ul2.Entity_Id = ed.Entity_Id )**
          and            
          ed.Entity_Id = ent.Entity_Id and  
          ed.Entity_Id = pr.Entity_Id and              
          ent.status = 1  and  ed.Fb_Id != '" . $prefRow['Fb_Id'] . "'  and 
          ed.Entity_Id NOT IN (select Entity2_Id from likes where Entity1_Id = '" . $entityId . "' and (Like_Flag = '1' OR  Like_Flag = '2' OR  Like_Flag = '3' OR  Like_Flag = '4'))             
          having 
          age BETWEEN '" . $prefRow['Preference_lower_age'] . "' AND '" . $prefRow['Preference_upper_age'] . "' LIMIT 10"; 
  </code></pre>
  
  <p>When I add the JOIN (mentioned in <strong>bold</strong>), i get only the partial result and NOT combining result from original query.</p>
  
  <p>I guess, I messed up <strong>AND</strong> or other important chaining operator somewhere.</p>
  
  <p><strong>Edit 2:</strong>: Getting the correct values now. Anyway to optimise it?</p>
  
  <pre><code>$getMatchesQry = "select DISTINCT ed.Entity_Id as entId,ed.Status as status,ed.First_Name,ed.Profile_Pic_Url,ed.Sex,ed.Current_Lat,ed.Current_Long,ed.community,ed.ethnicity,ed.Fb_Id,ent.Last_Active_Dt_Time,
    YEAR(NOW()) - YEAR(ed.dob) - (RIGHT(NOW(), 5) &lt; RIGHT(ed.DOB, 5)) as age, pr.Orientation
      from entity_details ed, entity ent, preferences pr, pref_occupation ul1, users_occ ul2, 
      where 
      (3959 * acos( cos( radians('" . $prefRow['Current_Lat'] . "') ) * cos( radians(ed.Current_Lat) ) * cos( radians(ed.Current_Long) - radians('" . $prefRow['Current_Long'] . "') ) + sin( radians('" . $prefRow['Current_Lat'] . "') ) * sin( radians(ed.Current_Lat) ) ) ) &lt;= " . $prefRow['Preference_radius'] . "          
      and
      (" . $pref_sex_str . ")  
      and
      (" . $prefComWord . ") and (" . $prefRelWord . ")
      and
      pr.Orientation  = '" . $prefRow['Orientation'] . "' 
      and             
      '" . $prefRow['min_height'] . "' &lt;= ed.height 
      and 
      ed.height  &lt;= '" . $prefRow['max_height'] . "'                    
      and
      ( ( ul1.Occupation_Id = ul2.Work_Id AND ul1.Entity_Id != ul2.Entity_Id AND ul2.Entity_Id = ed.Entity_Id ))        
      and            
      ed.Entity_Id = ent.Entity_Id and  
      ed.Entity_Id = pr.Entity_Id and              
      ent.status = 1  and  ed.Fb_Id != '" . $prefRow['Fb_Id'] . "'  and 
      ed.Entity_Id NOT IN (select Entity2_Id from likes where Entity1_Id = '" . $entityId . "' and (Like_Flag = '1' OR  Like_Flag = '2' OR  Like_Flag = '3' OR  Like_Flag = '4'))           
      having 
      age BETWEEN '" . $prefRow['Preference_lower_age'] . "' AND '" . $prefRow['Preference_upper_age'] . "' LIMIT 10"; 
  </code></pre>