Multiple relations and MySQL query
<p>I'm currently working on a databank project with mysql and php where one particular query is giving me an enormous headache.</p>

<p>In this project we have to search subcontractors for skills like java, c++ etc and evaluate if their skill rating is equal or grater to the rating value asked for.</p>

<p>We have a database scheme which looks something like that:</p>

<p>table subs:</p>

<ul>
<li>sub_id</li>
<li>sub_region_id</li>
</ul>

<p>table regions:</p>

<ul>
<li>region_id</li>
<li>region_name</li>
</ul>

<p>table skills:</p>

<ul>
<li>skill_id</li>
<li>skill_name</li>
</ul>

<p>table skillratings:</p>

<ul>
<li>skillrating_id</li>
<li>skillrating_value</li>
</ul>

<p>table subtoskill:</p>

<ul>
<li>subtoskill_id</li>
<li>subtoskill_sub_id</li>
<li>subtoskill_skill_id</li>
<li>subtoskill_raitng_id</li>
</ul>

<p>The subtoskill Table holds the N*N relations of subcontractors to skills,
so an entry with "1, 2, 3, 4" would state that the subcontractor with id 2
has the skill with id 3 and the rating with id 4.</p>

<p>Now I want to query multiple skills with an AND or OR connection,
for example "which subcontractors have skill java with value>=2 AND c++ with value>=4".</p>

<p>The OR query works perfectly(code below) but the AND query gives 0 results,
except if the same skill with different values is picked.</p>

<pre><code>/*
 * selectSubsBySkill
 * search database for subcontractors with given skills and ratings
 * parameter:
 * -$skill_ids array of ids(int) of skills, same size as $ratings
 * -$ratings array of rating values(int) for skills, same size as $skill_ids
 * -$and boolean true if and connection, else or connection of skills
 * return:
 * array with matching subcontractors information
 */
public function selectSubsBySkill($skill_ids, $ratings, $and){
    if(empty($skill_ids)){
        return $this-&gt;selectSubs();
    }
    $sel = 'SELECT * FROM '.SUBS.','.REGIONS.','.SKILLS.','.SUBTOSKILL.','.SKILLRATINGS.' WHERE '.
        SUB_REGION_ID.'='.REGION_ID.' AND '.SUBTOSKILL_SUB_ID.'='.SUB_ID.' AND '.
    SUBTOSKILL_SKILL_ID.'='.SKILL_ID.' AND '.SKILLRATING_ID.'='.SUBTOSKILL_RATING_ID.' AND (';

    $i=0;
    foreach($skill_ids as $skill_id){
        $sel .= '('.SKILLRATING_VALUE.'&gt;='.$ratings[$i].' AND '.SUBTOSKILL_SKILL_ID.'='.$skill_id.')';
        if($i&lt;sizeof($skill_ids)-1){
            if($and)$sel .= ' AND ';
            else $sel .= ' OR ';
        }
        $i++;
    }       
    $sel .= ') GROUP BY '.SUB_ID;
    return $this-&gt;query($sel);
}
</code></pre>

<p>I've tried several other examples including queries with UNIONs or JOINs but still nothing has worked.</p>

<p>I'd be glad if someone here got a hint to solve this problem.</p>