MySQL Counts with joins?

[Far Cry]

Not sure what version of MySQL my host is running.

 

What I am trying to do is basically display a users inventory and then count the amount of each item they have, however when I added the count in the query below it didn't work.

 

So I guess what I'm asking is, how do I use Joins with Counts?

 

Thanks in advance.

 

SELECT *  COUNT (item_name) AS amt FROM `items`LEFT JOIN `user_items` 

USING(`item_id`)

LEFT JOIN `user_equipped_items`

USING (`userid`)

WHERE `user_items`.`userid` = '$userid' 

AND `user_items`.`item_id` <> `user_equipped_items`.`primary_id`

AND `user_items`.`item_id` <> `user_equipped_items`.`secondary_id`

AND `user_items`.`item_id` <> `user_equipped_items`.`melee_id`

AND `user_items`.`item_id` <> `user_equipped_items`.`armor_id`

GROUP BY item_name

[gizmola]

Your query is invalid.

 

You can't have SELECT * COUNT.  Since the id is equivalent to the item_name why are you grouping on item_name and not item_id?  Not that it matters, but item_id is going to be keyed and indexed from the look of it.  Did you try:

 

SELECT count(*) as amt, item_name FROM ......