SELECT u.user_id, u.deck, u.name, u.status, u.url, u.ID
FROM (
SELECT ID, user_id, deck, name,
status , url, count( * ) AS user_count
FROM user_inventory
WHERE url NOT LIKE '%00.png'
AND name = '".mysql_real_escape_string($_COOKIE['mttcg']['u'])."'
AND status NOT IN ('mastered', 'double')
GROUP BY user_id, deck
) AS u
INNER JOIN (
SELECT deck, count( * ) AS card_count
FROM cards
GROUP BY deck
) AS c ON u.deck = c.deck
WHERE u.user_count = c.card_count
That's the SQL code i'm currently using which isn't doing what i want it to do
What its supposed to do is find out how many cards a user has then compare them to how many cards the set has
The problem is if someone has 21 cards, then the query brings back no results.
How would i modify the query so that it it detects to see if you have all the cards, event if you have doubles of a certain card
So with this situation
INSERT INTO `user_inventory` VALUES(3488, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru02.png', 'Mamoru 02', 'mamoru', 7, 4160, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3489, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru03.png', 'Mamoru 03', 'mamoru', 7, 4161, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3490, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru05.png', 'Mamoru 05', 'mamoru', 7, 4163, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3493, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru15.png', 'Mamoru 15', 'mamoru', 7, 4173, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3494, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru18.png', 'Mamoru 18', 'mamoru', 7, 4176, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3495, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru20.png', 'Mamoru 20', 'mamoru', 7, 4178, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3536, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru01.png', 'Mamoru 01', 'mamoru', 7, 4159, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3537, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru04.png', 'Mamoru 04', 'mamoru', 7, 4162, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3538, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru06.png', 'Mamoru 06', 'mamoru', 7, 4164, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3539, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru07.png', 'Mamoru 07', 'mamoru', 7, 4165, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3540, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru08.png', 'Mamoru 08', 'mamoru', 7, 4166, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3541, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru09.png', 'Mamoru 09', 'mamoru', 7, 4167, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3542, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru10.png', 'Mamoru 10', 'mamoru', 7, 4168, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3543, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru11.png', 'Mamoru 11', 'mamoru', 7, 4169, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3544, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru12.png', 'Mamoru 12', 'mamoru', 7, 4170, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3545, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru13.png', 'Mamoru 13', 'mamoru', 7, 4171, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3546, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru14.png', 'Mamoru 14', 'mamoru', 7, 4172, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3547, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru16.png', 'Mamoru 16', 'mamoru', 7, 4174, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3548, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru17.png', 'Mamoru 17', 'mamoru', 7, 4175, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(3549, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru19.png', 'Mamoru 19', 'mamoru', 7, 4177, 'collecting', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(6320, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru10.png', 'Mamoru 10', 'mamoru', 7, 4168, 'keeping', 1, 20, 'character', '');
INSERT INTO `user_inventory` VALUES(7599, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru04.png', 'Mamoru 04', 'mamoru', 7, 4162, 'double', 1, 20, 'character', 'HangMan');
INSERT INTO `user_inventory` VALUES(8032, 'SailorCapricornsama', 'http://midnighttempest.com/images/cards/mamoru12.png', 'Mamoru 12', 'mamoru', 7, 4170, 'double', 1, 20, 'character', 'Add Card');
It'd pick up the set was complete, but not pick up the last 3 results since they are doubles of existing cards, since at the moment, any double cards have to be set to doubles for it to work. I've tried to get this working mysql, but it still never picks anything up
I hope i've explained ok, i'm not good at explaining things
