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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Help needed on query on multiple tables

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:stroh@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=sstaples@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=gcdmg-mysql-2@m.gmane.org

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:stroh@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=sstaples@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=stroh@astroh.org
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Help needed on query on multiple tables

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:stroh@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:stroh@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=sstaples@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=stroh@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=gcdmg-mysql-2@m.gmane.org