Count rows nin a column from one table do not appear in another table

Question

I have TableA with 5 rows in column siteID ( so 5 different sites).

I have TableB that stores userIDs and which sites they have access to. I need to take all 5 siteIDs from TableA and check to see if any of the siteIDs are NOT in TableB for a specific user.

I'm trying something similar to this pseudocode but not sure how the syntax should go:

   SET @invalidSites = (
            SELECT COUNT(*) 
            FROM userSiteAccess AS usa
            RIGHT JOIN customer.sitesVsUsers AS cust
                ON usa.listOfSites = cust.siteID
            WHERE cust.siteID IS NULL);

EXAMPLE:

TABLE A
listOfSites
 1
 2
 3

TableB
userID   siteAccess
50       3

The count should return 2 as the list of sites has 2 additional rows (site 1 and site 2) but the user only has access to site 3.

You'll want to use `COUNT(DISTINCT cust.siteID)` to eliminate duplicates where different users cannot access the same site. — fubar, Jul 15 '19 at 21:33
@forpas I get no results whether I use `RIGHT JOIN` or `LEFT JOIN` i had it at right join when pasting because I'm experimenting. — , Jul 15 '19 at 21:35
Better edit your question and explain which table is which and also post sample data and expected results. — forpas, Jul 15 '19 at 21:36
Is `listOfSites` a comma-separated list? Then you need to use `FIND_IN_SET()`. See https://stackoverflow.com/questions/16208565/search-with-comma-separated-value-mysql/16210672#16210672 — Barmar, Jul 15 '19 at 21:38
@Barmar it is a comma-separated list.. i already parsed the list and created a table from it. The list has 5 sites which are now one site per row in `userSiteAccess` — , Jul 15 '19 at 21:40
The query you posted is not for a specific user, it's for all users. — Barmar, Jul 15 '19 at 21:42
@forpas I added an example of what the table would look like and the expected outcome. — , Jul 15 '19 at 21:47
@Barmar sorry for the confusion, the table has nothing that is comma separated. It is a regular table. I added an small example of what is happening and the expected outcome. — , Jul 15 '19 at 21:48
So you want tho count the sites that a specific user has not access? — forpas, Jul 15 '19 at 21:50
@forpas that's correct. I want to know if the list of sites has any siteIDs that are NOT valid for a specific user. — , Jul 15 '19 at 21:51

score 0 · Accepted Answer · answered Jul 15 '19 at 21:49

You need to use LEFT JOIN rather than RIGHT JOIN, and you need to specify the user ID in the ON condition.

SELECT COUNT(*)
FROM userSiteAccess AS usa
LEFT JOIN sitesVsUsers AS cust
    ON usa.listOfSites = cust.siteID AND cust.userId = 2
WHERE cust.siteID IS NULL

DEMO

forpas · Answer 2 · 2019-07-15T21:58:41.010

0

With NOT EXISTS:

select count(*) counter
from tablea a
where not exists (
  select 1 from tableb
  where userid = 50 and siteaccess = a.listofsites  
)

edited Jul 15 '19 at 21:58

answered Jul 15 '19 at 21:53

forpas

160,666
10
38
76

Count rows nin a column from one table do not appear in another table

2 Answers2