[RESOLVED] counting rows from 2 tables

[Desbrina]

I have 2 tables, both have a deck field
Tables: user_inventory + cards
I'm trying to make a system that automatically detects if a user has all then cards to a particular deck, so i need something that'll search through the user_inventory table, and pick out all the decks (deck is a field name in both the user_inventory table and the cards table) that have the same amount of rows in the user_inventory tables as well as the cards table

Example

INSERT INTO `user_inventory` (`ID`, `user_ID`, `name`, `item_id`, `deck`, `status`) VALUES 
(1, 1, 'User', 11, '', 'collecting'),
(2, 1, 'User', 23, '', 'collecting'),
(3, 1, 'User', 43, '', 'willtrade'),
(4, 1, 'User', 75, '', 'keeping'),
(5, 1, 'User', 114, '', 'keeping'),
(6, 1, 'User', 234, '', 'willtrade'),
(7, 1, 'User', 2701, 'layout1', 'collecting'),
(8, 1, 'User', 2702, 'layout1', 'collecting'),
(9, 1, 'User', 2703, 'layout1', 'collecting'),
(10, 1, 'User', 2704, 'layout1', 'collecting');

INSERT INTO `cards` (`ID`, `amount`, `type`, `url`, `deck`, `name`, `category`, `alttext`, `features`, `worth`, `deny`, `donated`, `created`) VALUES 
(2700, 15, 'puzzle', 'http://darknesswithin.com.ru/images/cards/shatterpoint15.png', 'shatterpoint', 'Shatterpoint', 'EU', 'shatterpoint 15', 'Shatterpoint', 1, '1', 'Desbrina', 0),
(2701, 4, 'special', 'http://darknesswithin.com.ru/images/cards/layout101.png', 'layout1', 'Layout 1', 'other', 'layout1 01', 'Images from Layout 1', 2, '0', 'Desbrina', 1),
(2702, 4, 'special', 'http://darknesswithin.com.ru/images/cards/layout102.png', 'layout1', 'Layout 1', 'other', 'layout1 02', 'Images from Layout 1', 2, '0', 'Desbrina', 1),
(2703, 4, 'special', 'http://darknesswithin.com.ru/images/cards/layout103.png', 'layout1', 'Layout 1', 'other', 'layout1 03', 'Images from Layout 1', 2, '0', 'Desbrina', 1),
(2704, 4, 'special', 'http://darknesswithin.com.ru/images/cards/layout104.png', 'layout1', 'Layout 1', 'other', 'layout1 04', 'Images from Layout 1', 2, '0', 'Desbrina', 1),
(2705, 20, 'character', 'http://darknesswithin.com.ru/images/cards/nofear01.png', 'nofear', 'No Fear', 'clone', 'nofear 01', '', 1, '0', 'Desbrina', 1),
(2706, 20, 'character', 'http://darknesswithin.com.ru/images/cards/nofear02.png', 'nofear', 'No Fear', 'clone', 'nofear 02', '', 1, '0', 'Desbrina', 1);

As you can see, both tables have 4 rows of layout 1

[Desbrina]

its been suggested that i use this

select
( select count(*) from cards where deck='layout1') -
( select count(*) from user_inventory where deck='layout1') as diff

but it wouldn't work, since i need to get the name of the deck, and doing it that way means i have to do over 100 queries sinces theres over 100 decks, whats the best way to go about it?

[tomhath]

select u.user_id, u.deck
from (select user_id, deck, count(*) as user_count
      from user_inventory 
      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

[Desbrina]

thanks, works perfectly