[RESOLVED] comparing rows from 2 tables help

[Desbrina]

I have a join statement thats not working right, which is an old one that i've tried to edit

 SELECT c.deck, u.status, u.name, u.user_count, c.card_count
FROM (

SELECT cards.deck, user_inventory.name, user_inventory.status, user_inventory.card_id, count( DISTINCT user_inventory.card_id ) AS user_count
FROM user_inventory, cards
WHERE cards.ID = user_inventory.card_id
AND user_inventory.name = 'Desbrina'
AND user_inventory.status NOT
IN (
'double', 'mastered'
)
GROUP BY cards.deck
) AS u
INNER JOIN (

SELECT DISTINCT deck, ID, count( cards.ID ) AS card_count
FROM cards
GROUP BY deck
) AS c ON u.user_count = c.card_count 

I have 2 tables

CREATE TABLE IF NOT EXISTS `user_inventory` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL COMMENT 'Name of the user',
  `user_id` int(11) NOT NULL COMMENT 'Id of the user from the memberlist table',
  `card_id` int(11) NOT NULL COMMENT 'ID of the card from the cards table',
  `status` varchar(255) NOT NULL,
  `from` varchar(255) NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `card_id` (`card_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=23809 ;

ALTER TABLE `user_inventory`
  ADD CONSTRAINT `user_inventory_ibfk_1` FOREIGN KEY (`card_id`) REFERENCES `cards` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS `cards` (
  `ID` int(255) NOT NULL auto_increment,
  `amount` int(4) NOT NULL,
  `type` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `deck` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `category` varchar(255) NOT NULL,
  `alttext` varchar(255) NOT NULL,
  `features` varchar(255) NOT NULL,
  `worth` int(1) NOT NULL,
  `deny` int(3) NOT NULL,
  `donated` varchar(255) NOT NULL,
  `created` tinyint(1) NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `worth` (`worth`),
  KEY `deny` (`deny`),
  KEY `cards_idx_worth_deny` (`worth`,`deny`),
  KEY `alttext` (`alttext`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9903 ;

I'm trying to get the amount of rows in the user_inventory table that have a matching amount of rows in the cards table. If they match, then i want to know the deck

For some reason though, decks that have status set to mastered are showing up, but showing up as willtrade or keeping, even through looking through the table theres not any that are either.

The decks also show up more than once

Please help me fix it

[Lars_Berg]

select c.deck, u.status, u.name, u.user_count, c.card_count
   from (select cards.deck, 
                       user_inventory.name, 
                       user_inventory.status, 
                       count(distinct user_inventory.card_id ) as user_count
               from user_inventory, cards
            where cards.ID = user_inventory.card_id
                and user_inventory.name = 'Desbrina'
                and user_inventory.status not in ('double','mastered') 
             group by cards.deck,user_inventory.name) AS u
   join 
        (select deck,count(*) as card_count
           from cards
          group by deck) as c 
   on u.user_count = c.card_count 
  and u.deck = c.deck

All non-aggregated columns in the select clause must either be in the group by clause or be functionally dependent on a column in the group by clause. Mysql does not enforce this requirement and hence you get strange results.

You also forgot the join condition on deck.

[Desbrina]

thank you, i understand now