[TYPO3-mvc] many to many relations, with 2 categories

[Philipp Wrann]

>  I need to know how many records there are for each category.

select uid_foreign, uid_local, count(uid_local) from relationtable group by uid_local;

Categories not allready related are not in the result

so:
select uid from categories where uid not in (###UIDLIST###)

where uidlist is the uid_local list from the first result

gives you additionally all categories not assigned to an object

> But how do I select all records that have cat 1 AND cat 2

With extbase simply add a logicalAnd constraint to your query, effectively that results in

select * from table where uid in (select uid_local from relationtable where uid_foreign = UID_ONE) and uid in (select uid_local from relationtable where uid_foreign = UID_TWO);