MySQL join 3 tables and find "missing" rows in one table

debugcn Published at Dev

bhttoan

I have 3 tables in MySQL and I need to find all instances where a user does not have a specific widget.

Example:

Users
tenant_id user_id user_name
1         1       Bob
1         2       Fred
1         3       John
1         4       Tom

Widgets
tenant_id widget_id widget_name
1         1         Red
1         2         Blue
1         3         Green
1         4         Black

Usage
tenant_id user_id widget_id
1         1       1
1         1       2 
1         1       4
1         2       2
1         2       3
1         2       4
1         3       1
1         3       2
1         3       3
1         3       4
1         4       1
1         4       2
1         4       3

Missing in table three are:
user_name widget_name
Bob       Green
Fred      Red
Tom       Black

The query I am trying to use is:

SELECT
  user_name, widget_name
FROM
  users left join widgets on users.tenant_id=widgets.tenant_id
WHERE 
  NOT EXISTS (
    SELECT 1 FROM usage WHERE user_id = users.user_id AND widget_id = widgets.widget_id
  ) and users.tenant_id=1

When the query completes it brings back a huge list of usernames and widget names but many hundreds more than I expect.

I am not sure if the join is wrong or if I need to do some sort of grouping on the result?

Gordon Linoff

Here is the idea behind this type of query. First generate all possible combinations of users and widgets. Then filter out the ones that exist:

select u.user_name, w.widget_name
from users u join
     widgets w
     on u.tenant_id = w.tenant_id
where not exists (select 1
                  from usage us
                  where us.user_id = u.user_id and us.widget_id = w.widget_id and
                        us.tenant_id = u.tenant_id
                 ) and
      u.tenant_id = 1;

I think your logic is missing the tenant_id in the usage table. However, I'm not sure that makes a big difference.

Collected from the Internet

Please contact [email protected] to delete if infringement.