joining two tables in mysql

rsiedl has asked for the wisdom of the Perl Monks concerning the following question:

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;
[download]

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

Comment on joining two tables in mysql Download Code

Replies are listed 'Best First'.
Re: joining two tables in mysql by Corion (Patriarch) on May 15, 2006 at 09:13 UTC
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` [download] 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.	[reply] [d/l] [select]
Re: joining two tables in mysql by tinita (Parson) on May 15, 2006 at 09:15 UTC
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	[reply] [d/l] [select]
Re: joining two tables in mysql by UnderMine (Friar) on May 15, 2006 at 09:59 UTC
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` [download] 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	[reply] [d/l]