From: Peter Brawley
Date: October 23 2010 12:22pm
Subject: Re: Adding a subquery
List-Archive: http://lists.mysql.com/mysql/223419
Message-Id: <4CC2D382.2070305@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 > 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.
>