/me sees only posts from jptxs, scratches head...

Umm, glad we could help? =)

BTW, in the future you probably don't want to do what you did in Table 1. Putting a list in a field with commas is pure, unadulterated evil. The database gods will forsake you for such blasphemy.

Many-to-many lookups are best accomplished with cross-tables:

Table1:
 Act  | ...
 __________
 Act1 | ...
 Act2 | ...
 Act3 | ...
 Act4 | ...

Table2:
 id | name
 _________
 1  | foo
 2  | bar
 3  | this
 6  | that
 8  | hey

XTable12:
 Act  | id
 __________
 Act1 | 1
 Act1 | 2
 Act2 | 3
 Act2 | 2
 Act3 | 6
 Act4 | 2
 Act4 | 8

Its going to suck if some person comes along behind ya and wants to stick 5 things in that list and there are only 8 chars allowed. =) I should know, I had to fix this for someone once (And we paid them hundreds of thousands of dollars for it... grr...)

--
$you = new YOU;
honk() if $you->love(perl)

While I agree that it's evil, I didn't have anything to do with it. I just have to deal with it now : )

However, I don't see how your suggestion could deliver the same functions. Basically, the columns in Table1 are named for actions one could perform on an appointment and the comma seperated lists are the products one would perform those actions for on a given appointment which is detailed by that row. Though the number of actions is fixed (relatively), the products vary with every appointment. so how does your structure above provide the ability to have varying products associated with any number of actions and also correlated to one another as a coherent appointment? I've been staring at your tables and can't quite get it...

-- I'm a solipsist, and so is everyone else. (think about it)

Simple - table 1 contains your actions:

id   action
---------------
 1     ls
 2     cd
 3     file
 4     zonk
[download]

Table 2 contains your list of products:

id   product
-----------------
 1     /usr/bin
 2     /home
 3     /usr/tmp
[download]

And finally Table 3 is a cross reference of the two, called product_actions:

p_id   a_id
-------------
 2      3
 1      3
 2      4
[download]

so something like:

SELECT actions.action, products.product
FROM   actions, products, product_actions
WHERE  actions.id = product_actions.a_id
AND    product.id = product_actions.p_id
[download]

Would produce:

file /home
file /usr/bin
zonk /home  #don't try this one at home kids!
[download]

At least that's what I think that extremely had in mind. Your results will vary, and from what I hear you saying, it sounds to me like you are working with a very un- normalized database.

Hope this helps,
Jeff

that whole seen thing i was doing was uneeded as I only get one row, and it was obfuscating all the real issues. I took that out (which makes the code less robust as it only handles one row at a time, but that's all i need) and now it works. here the fixed code:

my %newhash;

while ( my $myRef = $test->fetchrow_hashref ) {
        foreach my $key ( keys %$myRef ) {
                next if ( $key eq "TRANS_ID" );
                my @tryit = split ",", $myRef->{$key};
                my $name;
                foreach my $number ( @tryit ) {
                        $name = $dbh->selectrow_array("SELECT PRODUCT 
+FROM SC_PRODUCT WHERE PRODUCT_ID = $number");                        
                        push @{$newhash{$name}}, $key;
                }
        }
}

foreach my $key ( keys %newhash ) {
        print "$key : @{$newhash{$key}}\n";
}
[download]

-- I'm a solipsist, and so is everyone else. (think about it)

UPDATE: the output should be:

foo : act1
bar : act1, act2, act4
this : act2
that : act3
hey : act4
[download]

-- I'm a solipsist, and so is everyone else. (think about it)