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.
Thanks again.
Albert Padley
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.
yes?
On 7/22/05, Albert Padley <[EMAIL PROTECTED]> 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
available)
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
columns:
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.
Thanks.
Albert Padley
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
