MYSQL: How to find out the qty of unequipped items?

Question

Here is my DB Structure:

CREATE TABLE IF NOT EXISTS `UserItems` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `user_id` int(10) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `qty` int(11) NOT NULL default '0'
) ;



CREATE TABLE IF NOT EXISTS `UserEquippedItems` (
  `user_id` int(10) unsigned NOT NULL,
  `user_item_id` bigint(20) unsigned NOT NULL
);


CREATE TABLE IF NOT EXISTS `UserFriendEquippedItems` (
  `user_friend_id` int(10) unsigned NOT NULL,
  `user_item_id` bigint(20) unsigned NOT NULL
); 

UserItems keeps all the item inventory with quantity.
let say if I have 5 item (item id 123456). then the entry will be (null, $userid, 123456, 5). the qty is the quantity of all the entity with the same item_id.

However, some Users may equip the item. Some do not. If they equip it, it will be an entry in UserEquippedItems table.

Also, users' friends can equip the user's item too.

Sample Data:

UserItems:
id, user_id,  item_id, qty
( 1, 4567,   123123123, 5)
( 2, 4567,   100010001, 2)
( 3, 4567,   100010099, 1)


UserEquippedItems:  (user_item_id is UserItems.id)
user_id, user_item_id
( 4567, 1)
( 4567, 2)

UserFriendEquippedItems
(user_item_id is UserItems.id)
user_friend_id, user_item_id
( 4100, 1)
( 4100, 3)

So, how can I find out the quantity of items that are equipped? and how can I find out the quantity of items that are NOT equipped ?

Side Story: Before, we have each individual UserItems as a single entry in the DB. e.g. for item_id = 123123123, if I have 5 of them, i have 5 entries in the DB. But then, our DB grows like crazy to 4 million UserItems records. that's why instead of having 5 entries, we only have one entry in the UserItems table, with the qty field for keep track how many in total. I don't know if it is the right approach, but I hope it can cut down 75% of the DB size.

Also, it was the query to get the unequipped items:

    SELECT Items.id, count(UserItems.id) as numCount 
FROM UserItems INNER JOIN 
Items ON UserItems.active=1 AND 
UserItems.item_id=Items.id AND 
Items.active=1 AND 
UserItems.user_id=$userId  
WHERE NOT EXISTS ( 
 SELECT UserEquippedItems.user_item_id 
 FROM UserEquippedItems  
 WHERE UserEquippedItems.user_item_id= UserItems.id
)  
AND NOT EXISTS ( 
 SELECT UserFriendsEquippedItems.user_item_id 
 FROM UserFriendsEquippedItems 
 WHERE UserFriendsEquippedItems.user_item_id= UserItems.id) 
GROUP BY Items.id

Of course, this query doesn't work with the new schema. :)

Answer 1

To count the total number of items equipped:

SELECT count(*) FROM UserEquippedItems e JOIN UserItems i ON (i.id = e.user_item_id)

To find the total number of items of id $item_id equipped

SELECT count(*) FROM UserEquippedItems e WHERE e.user_item_id = $item_id

And to find the total number of items user $user_id has equipped (my first response, though probably not what you're after)

SELECT count(*) FROM UserEquippedItems WHERE user_id = $user_id

Answer 2

This should work:

SELECT id, user_id, item_id, qty,
    (SELECT COUNT(*) FROM UserEquippedItems uei 
        WHERE uei.user_item_id=ui.id) as qty_equipped, 
    (SELECT COUNT(*) FROM UserFriendEquippedItems ufei 
        WHERE ufei.user_item_id=ui.id) as qty_friend_equipped
FROM UserItems ui 

Then to get the unequipped, on your client you can subtract the qty_equipped and qty_friend_equipped from qty.

A single query that just returns unequipped:

SELECT id, user_id, item_id, qty,
    qty-
        (SELECT COUNT(*) FROM UserEquippedItems uei 
            WHERE uei.user_item_id=ui.id)-
        (SELECT COUNT(*) FROM UserFriendEquippedItems ufei 
            WHERE ufei.user_item_id=ui.id) 
    as qty_unequipped
FROM UserItems ui 

You could combine those 2 queries above into one big query, but I image that would hurt performance, since it will run the sub-queries twice.

You can add a WHERE clause on the end of both of these to return results for a specific user / item.