Count query from 2 tables <p>I have 2 tables like this. For a given attribute (in table doc_attribute) and a given 'word' in file (in document), I want to find the count of distinct doc_id that do not contain that particular attribute but contain that 'word' in file.</p> <p>For example if the attribute given is camera and 'word' is mobile, then the result should be 2. (ie., 2nd, 3rd and 5th doc_id does not contain camera and among them only 2nd and 3rd contain the 'word' mobile in file)</p> <pre><code>table doc_attribute +--------+-----------+--------+ | doc_id | attribute | value | +--------+-----------+--------+ | 1 | product | mobile | | 1 | model | lumia | | 1 | camera | 5mp | | 2 | product | mobile | | 2 | model | lumia | | 2 | ram | 1gb | | 3 | product | mobile | | 3 | year | 2014 | | 3 | made-in | china | | 4 | brand | apple | | 4 | model | iphone | | 4 | camera | 5mp | | 5 | product | camera | | 5 | brand | canon | | 5 | price | 20000 | table document +--------+-----------------------------+ | doc_id | file | +--------+-----------------------------+ | 1 | lumia 5mp mobile 1gb 2014 | | 2 | lumia 8mp mobile 1gb 2015 | | 3 | galaxy mobile fullhd 2gb | | 4 | iphone apple 5mp 2013 new | | 5 | canon 20000 new dslr 12mp | </code></pre> <p>Current query:</p> <pre><code>select count(doc_id) from document where doc_id not in (select doc_id from doc_attribute where attribute = 'camera') and file REGEXP '[[:&lt;:]]mobile[[:&gt;:]]'; </code></pre>