How to compare two sets

I need to get all aid‘s values which have cid != 40 and cid= 39 (many-to-many connection)

I’ve prepared two queries but i don’t know how to except second from first

SELECT DISTINCT aid FROM ac_branch WHERE aid IN (SELECT aid FROM ac_branch WHERE cid = 39);

SELECT DISTINCT aid FROM ac_branch WHERE aid IN (SELECT aid FROM ac_branch WHERE cid != 40);

Answer

It looks like you’re actually pretty close, is this what you’re looking for?

SELECT DISTINCT aid 
FROM ac_branch 
WHERE cid = 39 
   AND aid NOT IN (SELECT aid FROM ac_branch WHERE cid = 40)
;

---

Alternatively, you can do it without subqueries like so:

SELECT aid 
FROM ac_branch
WHERE cid IN (39, 40)
GROUP BY aid
HAVING SUM(CASE WHEN cid=39 THEN 1 ELSE -1 END) = 1
;

Though, this assumes there can only be single entries for 39 and 40 for a given aid value. For example if there were two entries with aid=1 and cid=39, and none with cid=4, aid=1 would not appear (because the sum would be 2). Similarly, an aid with two 39’s and one 40 would also be included in the results.

---

A third option involves a JOIN

SELECT DISTINCT acb39.aid
FROM ac_branch AS acb39
LEFT JOIN ac_branch AS acb40
   ON acb39.aid = acb40.aid AND acb40.cid = 40
WHERE acb1.cid = 39
   AND acb40.aid IS NULL
;