I am glad that I was able to help someone finally :) There may be other ways to do this, but that was what first came to mind. I would maybe run an explain on that query to ensure that it is using indexes.
Steven Staples > -----Original Message----- > From: Michael Stroh [mailto:st...@astroh.org] > Sent: June 3, 2010 11:55 AM > To: Steven Staples > Cc: 'MySql' > Subject: Re: Help needed on query on multiple tables > > Thanks! That did it perfectly! > > Michael > > > On Jun 3, 2010, at 11:45 AM, Steven Staples wrote: > > > How about this? > > > > SELECT > > `first_table`.`names` > > , `first_table`.`version` > > , (SELECT > > COUNT(`other_table`.`names`) > > FROM `other_table` > > WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count' > > FROM `first_table` > > WHERE `first_table`.`progress` > 0; > > > > > > Granted, you have not provided structure or names of the tables so this > is > > just my interpretation, but maybe something like this could give you a > > starting point? > > > > Steven Staples > > > > > >> -----Original Message----- > >> From: Michael Stroh [mailto:st...@astroh.org] > >> Sent: June 3, 2010 11:24 AM > >> To: MySql > >> Subject: Help needed on query on multiple tables > >> > >> Hi everyone. I'm trying to create a certain MySQL query but I'm not sure > >> how to do it. Here is a stripped down version of the result I'm aiming > > for. > >> I'm pretty new to queries that act on multiple tables, so apologize if > > this > >> is a very stupid question. > >> > >> I have one table (data) that has two columns (names and progress). I > have > > a > >> second table (items) that has two columns (names and version). I'd like > to > >> do a query that produces the name of every record in data that has > > progress > >> set to 0 and the number of records in the items table that have the same > >> value in each table.names field. > >> > >> I can perform this by using two sets of queries, one that queries the > data > >> table and then loop through the names to do a count(names) query, but > I'm > >> not sure if I can somehow do it in one query. > >> > >> Thanks in advance! > >> Michael > >> > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net > >> > >> No virus found in this incoming message. > >> Checked by AVG - www.avg.com > >> Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10 > >> 02:25:00 > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql?unsub=st...@astroh.org > > > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10 > 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
