I'm having real difficulty figuring out how to use a subquery in another
query. In fact, I'm not even sure if I do need a subquery or if I can
accomplish what I want some other way.
Running:
Server version: 5.1.49-community-log
Protocol version: 10
MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 300533 $
I have the following table:
CREATE TABLE IF NOT EXISTS `scoresUncharted` (
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
`map` varchar(32) NOT NULL,
`user` varchar(15) NOT NULL,
`group` varchar(20) NOT NULL,
`score` mediumint(8) unsigned NOT NULL,
`playCount` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`map`,`user`),
KEY `ts` (`ts`),
KEY `map` (`map`),
KEY `user` (`user`),
KEY `group` (`group`),
KEY `score` (`score`),
KEY `playCount` (`playCount`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `scoresUncharted`
--
INSERT INTO `scoresUncharted` (`ts`, `map`, `user`, `group`, `score`,
`playCount`) VALUES
('2010-08-14 11:39:14', '733997', 'Karsten75', 'Marauders', 8243, 1),
('2010-08-22 09:08:13', '734370', 'Sauffaus3', 'Resubmitted', 7619, 1),
('2010-08-14 12:07:57', '730003', 'Karsten75', 'Marauders', 8647, 1),
('2010-08-14 16:26:58', '714566', 'Karsten75', 'Marauders', 9240, 1),
('2010-08-15 00:59:46', '733996', 'Karsten75', 'Marauders', 8139, 1),
('2010-08-15 11:02:53', '0', 'Karsten75', '', 9295, 3),
('2010-08-16 02:34:01', '733999', 'Karsten75', '', 9111, 1),
('2010-08-16 02:52:38', '733998', 'Karsten75', '', 8251, 1),
('2010-08-16 13:46:08', '730370', 'Karsten75', '', 8452, 1),
('2010-08-17 16:56:27', '734000', 'Karsten75', '', 8744, 1),
('2010-08-18 01:13:34', '368757', 'Karsten75', '', 8440, 1),
('2010-08-18 01:54:22', '405282', 'Karsten75', '', 7139, 1),
('2010-08-19 15:37:58', '734001', 'Karsten75', '', 8579, 1),
('2010-08-19 15:57:55', '734002', 'Karsten75', '', 7746, 1),
('2010-08-19 16:19:40', '734004', 'Karsten75', '', 7964, 1),
('2010-08-22 09:07:27', '734370', 'Sauffaus2', '', 7619, 1);
What I am trying to do is to pull up a report that shows how many
players have played one or more maps in each of the groups.
I tried this query:
SELECT scoresuncharted.group,
COUNT(scoresuncharted.user) AS players
FROM scoresuncharted
GROUP BY scoresuncharted.group
HAVING (Not scoresuncharted.group='')
ORDER BY players DESC;
But this gives me the number of maps played by all users using that group.
I tried DISTINCT, but I could nt tell that it made any difference.
This query got me each player playing in a particular group:
select Distinct scoresuncharted.user, scoresuncharted.group
from scoresuncharted
having (not scoresuncharted.group='')
order by scoresuncharted.group
My thinking is that if I could somehow shoehorn the second query into
the first as a subquery, I might get the results I desire.
Trying that, I first got an error because the subquery contained
multiple columns, then I changed it and I got an error because the
result of the subquery contained multiple rows!
I'm kind of stuck in the manual, since I don't know my way around it
well enough. It describes the syntax, but the examples doesn't seem to
apply to what I'm trying to do.
Can anyone here perhaps give me some guidance?
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
