> 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.
See "All X for which all Y is Z" at
http://www.artfulsoftware.com/infotree/queries.php.
PB
-----
On 10/22/2010 6:12 PM, MikeB wrote:
> 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.
>
