joining two tables in mysql

[rsiedl]

hi monks,

i've written the following code which basically joins two the data from two mysql tables based on an id in the first table.

        my $join = 'customer_id';
        my $sth = $global{DBH}->prepare(" select * from voice_calls ")
+;
        my $cth = $global{DBH}->prepare(" select * from customers wher
+e $join=?");
        $sth->execute();
        while ( my $call = $sth->fetchrow_hashref() ) {

                $cth->execute( ${$call}{$join} );
                my $customer = $cth->fetchrow_hashref();
                $cth->finish;

                foreach my $key (keys %$customer) {
                        ${$call}{$key} = ${$customer}{$key}
                } # end-foreach

                push(@{$self->{RESULTS}}, \%{$call});

        } # end-while
        $sth->finish;

i'm pretty sure there is a way to do this straight from a select in mysql and was wondering if anybody knew it?

cheers,
reagen

[Corion]

You want the SQL JOIN command, which does just what your code does, except in SQL, and returns the joined results.

select * from voice_calls
left join customers
on customers.customer_id = voice_calls.customer_id

The statement doesn't return any calls for which no customer was found, so make sure you have proper constraints on your voice_calls table, so that every call has a valid customer_id enforced.

I'm not really sure where your question touches Perl though - a Google search for SQL JOIN directly brings up relevant pages.

[tinita]

this should get you started:
SELECT * FROM voice_calls INNER JOIN customers ON customers.id = voice_calls.customer_id
(note that mysql will not prefix the result column names with the table name.)

by the way, searching the mysql-docs is easy, in most cases you can just type dev.mysql.com/your_search_keyword, so
http://dev.mysql.com/join leads to
http://dev.mysql.com/doc/refman/5.0/en/join.html

[UnderMine]

There are two forms of join syntax. The first is the JOIN command explained above. The other is to use the where clause

SELECT * 
  FROM voice_calls ,
       customers 
 WHERE customers.id = voice_calls.customer_id

In order to get unique column names you will need to look up aliasing in the SQL manuals. MySQL SELECT Syntax

Hope this helps

UnderMine