SQL Server 2005 How to find the user who has all the books
I am trying to make a request to that user who has the entire computer book. For example, I have two tables:
1) Table of elements
|itemid |item_name |
------------------------------
|1 | computerbook1 |
|2 | computerbook2 |
|3 | computerbook3 |
|4 | mathbook1 |
|5 | mathbook2 |
|6 | physicsbook |
2) userinventory table
|used_id | name_item |
-----------------------------
|1 | computerbook1 |
|1 | computerbook2 |
|1 | computerbook3 |
|2 | computerbook1 |
|2 | mathbook1 |
|2 | physicsbook |
|3 | computerbook1 |
|3 | computerbook3 |
Since user "1" has the entire computer book, I would like to make a query that returns user 1.
What I did was ...
Create Table #tmp (
Classname [varchar](50) NOT NULL
)
INSERT INTO #tmp
SELECT DISTINCT item_name
FROM ITEM
WHERE item_name like 'computerbook%'
I would like to compare to find a user who has the whole item.
However, I am not sure how to do this .. because I cannot use count or any aggregation.
Don't use "count"
Anyway, knows how to find the ID of the user who has all the computer books?
source to share
The key to my solution is the except clause. I cross-connect all user / book capabilities and then - with an except clause - I get a list of all users that don't qualify for those capabilities. This is A = all capabilities, B = actual data, and A, other than B, will contain only those users who are missing rows in A. From there I query for all users presented in the inventory table that are not among those - query. I admit it is quite confusing and can use temporary table breakdown.
SELECT user_id
FROM userinventory
EXCEPT
(
SELECT user_id
FROM (
SELECT UX.user_id, II.item_name
FROM item II, (SELECT UU.user_id FROM userinventory UU) UX
WHERE II.item_name LIKE 'computerbook%'
EXCEPT
SELECT UU.user_id, UU.name_item
FROM userinventory UU
) XX
)
source to share
Something like that:
SELECT DISTINCT userid FROM userinventory WHERE NOT EXISTS (SELECT null
FROM item WHERE NOT EXISTS (SELECT null FROM userinventory i2
WHERE i2.name_item = item.item_name AND i2.user_id = userinventory.userid))
In other words, we are looking for individual users who own at least one item, where there is no item that they do not have.
source to share
declare @Item table(itemid int, item_name varchar(30))
insert @item values(1, 'computerbook1')
insert @item values(2,'computerbook2')
insert @item values(3,'computerbook3')
insert @item values(4,'mathbook1')
insert @item values(5,'mathbook2')
insert @item values(6,'physicsbook')
declare @userinventory table(user_id int, name_item varchar(30))
insert @userinventory values(1,'computerbook1')
insert @userinventory values(1,'computerbook2')
insert @userinventory values(1,'computerbook3')
insert @userinventory values(2,'computerbook1')
insert @userinventory values(2,'mathbook1')
insert @userinventory values(2,'physicsbook')
insert @userinventory values(3,'computerbook1')
insert @userinventory values(3,'computerbook3')
;with users as
(
select distinct user_id from @userinventory
), books as
(
select item_name from @item
where item_name like 'computerbook%'
), missingbooks as
(
select * from users cross join books
except
select user_id, name_item from @userinventory
)
select user_id from users
where user_id not in (select user_id from missingbooks)
source to share