mySQL exclusive records

I have many ClientIDs to many pageids

Eg.

ClientID 1 PageID 3

ClientID 1 PageID 2

ClientID 2 PageID 3

ClientID 3开发者_运维技巧 PageID 3

In a query I want to bring up the record with only PageID 3 exclusively.

So in my result i should get Client 2 and 3 and client 1 should be omitted out of the results... Whats the best way to do this?

Try this one -

SELECT clientid FROM
  clients
GROUP BY
  clientid
HAVING
  COUNT(*) = COUNT(IF(PageID = 3, PageID, NULL));

You could try something like this ...

SELECT DISTINCT ClientID
FROM table
WHERE PageID = 3
AND ClientID NOT IN ( SELECT DISTINCT ClientID FROM table WHERE PageID != 3 )

Or this ...

SELECT DISTINCT a.ClientID
FROM table a
JOIN ( SELECT ClientID, COUNT(*) AS total FROM table GROUP BY ClientID ) b ON a.ClientID = b.ClientID
WHERE b.total = 1
AND a.PageID = 3

Don't work see comment below !

SELECT ClientID FROM table WHERE PageID=3 GROUP BY ClientID HAVING count(ClientID)=1

This the idea, try something around.

Hope this help.