Count how many rows have no relationship with a table under x conditions-CodePudding

I have a users table and a hobbies table

users

id | name   |
--- -------- 
1  | John   |
2  | Jim    |
3  | Karen  |

hobbies

id | user_id | hobby   |
--- --------- --------- 
1  | 1       | drawing |
2  | 1       | singing |
3  | 2       | coding  |
4  | 2       | drawing |
6  | 3       | chess   |
7  | 3       | coding  |

I need an SQL query that can count how many users DON'T have 'drawing' or 'singing' as a hobby. In this example, only Karen would be counted, as they are the only one who does not like singing or drawing.

CodePudding user response：

You can use not exists . . . twice:

select u.*
from users u
where not exists (select 1 from hobbies h where h.user_id = u.id and h.hobby = 'singing') and
      not exists (select 1 from hobbies h where h.user_id = u.id and h.hobby = 'dancing') ;

CodePudding user response：

You can use the not exists operator:

SELECT *
FROM   users u
WHERE  NOT EXISTS (SELECT *
                   FROM   hobbies h
                   WHERE  hobby IN ('drawing', 'singing') AND
                          h.user_id = u.id)

CodePudding user response：

easy

declare @users table(id int, name varchar(15))
declare @hobbies table(id int, user_id int, hobby varchar(15))

insert into @users
values
 (1,'John'),
 (2,'Jim'),
 (3,'Karen')

insert into @hobbies
values
 (1,1,'drawing'),
 (2,1,'singing'),
 (3,2,'coding'),
 (4,2,'drawing'), 
 (6,3,'chess'),
 (7,3,'coding')


select count(*)
from @users u
left join
(select u.id
from @users u
    inner join @hobbies h
    on h.user_id = u.id
where h.hobby in ('drawing','singing'))users_with_hobbies  on users_with_hobbies.id = u.id
where users_with_hobbies.id is null

-- or

select count(*)
from @users u   
where not id in (select u.id
   from @users u
    inner join @hobbies h
    on h.user_id = u.id
where h.hobby in ('drawing','singing'))

-- or   
select count(*)
from @users u   
where not exists (select null
  from @hobbies h   
  where h.user_id = u.id and h.hobby in ('drawing','singing'))