List:General Discussion« Previous MessageNext Message »
From:MikeB Date:October 22 2010 11:12pm
Subject:Adding a subquery
View as plain text  
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.
Thread
Adding a subqueryMikeB23 Oct
  • Re: Adding a subqueryPeter Brawley23 Oct