Sol and Peter,
Thanks for your feedback. Both of your suggestions got me going in
the right direction and I was able to solve the problem using
temporary tables and left joins.
On Jul 23, 2005, at 11:00 AM, sol beach wrote:
> create table count_temp1 select id, count(id) count_id from table ss;
> create table count_temp 2 select id, count(tt) from ss where id = sd1
> or id = sd2;
> should get you closer.
> On 7/22/05, Albert Padley <apadley@stripped> wrote:
>> I would be grateful if those of you around this weekend could help me
>> figure out if what I'm after is possible. I've already spent hours
>> with the manual, the archives and my books. I've looked at JOINS and
>> TEMP TABLES but still can't come up with a solution.
>> THE ENVIRONMENT: MySQL Version 4.0.24 (so subselects are not
>> THE TABLE
>> CREATE TABLE `ss` (
>> `tt` INT NOT NULL AUTO_INCREMENT ,
>> `zz` INT( 3 ) NOT NULL ,
>> `sd1` INT( 3 ) NOT NULL ,
>> `sd2` INT( 3 ) NOT NULL ,
>> PRIMARY KEY ( `id` )
>> THE ISSUE:
>> 1. xx, sd1 and sd2 all contain id numbers. These numbers are unique
>> within each record.
>> 2. I need to scan the table and create a table row for each id number
>> in zz that contains:
>> a. how many times each id appears in zz (This is easy using COUNT)
>> b. how many times each id appears in sd1 plus sd2. (If a total is
>> not possible, then a separate listing for sd1 and sd2 would suffice
>> (just like we have for zz)
>> c. The final table should list each id number with the following
>> id number
>> total times id number appears in zz column
>> total times id number appears in sd1 & sd2 combined
>> I sure hope this makes sense.
>> Oh, one more thing. I can't change the table structure because I have
>> simplified it here for finding a solution. The above columns are part
>> of a much larger table that is currently in use for other purposes.
>> Albert Padley
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?