SQL select rows that have one value but not another
<p>I have a table in SQL which will contain multiple rows for one id, as below</p>

<pre><code>accountid   Productname
1           GL
1           IP
1           MI
2           GL
2           IP
2           PA
3           MI
3           CP
3           IP
4           GL
4           CP
4           CI
</code></pre>

<p>I want to be able to select all accounts which have certain products but not other. For example all that have IP or GL but not MI, using the sample table above this would return accounts 2 and 4.</p>

<pre><code>SELECT ccx_accountidname
FROM (
    SELECT ccx_accountidname, ccx_productname
    FROM Filteredccx_leadresearch
    WHERE ccx_productname IN ('GL','IP')
    AND ccx_accountidname IS NOT NULL
    ) AS T
WHERE ccx_productname NOT IN ('MI')
ORDER BY ccx_accountidname
</code></pre>

<p>and</p>

<pre><code>SELECT DISTINCT LR1.ccx_accountidname
FROM Filteredccx_leadresearch LR1
LEFT JOIN Filteredccx_leadresearch LR2 ON LR1.ccx_accountid =   LR2.ccx_accountid 
AND LR2.ccx_productname IN ('GL', 'IP')
WHERE LR1.ccx_productname NOT IN ('MI')
AND LR1.ccx_accountidname IS NOT NULL
ORDER BY LR1.ccx_accountidname
</code></pre>

<p>Both give basically the same results, is there any way this can be done?</p>

<p>Thanks in advance for any help</p>