2 mysql selects in one statement

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

I'm not very fluent with MySQL and I ask that you don't toss some advanced techniques my way.

I've run into a problem. I have 2 tables in my database that I need to SELECT from at the same time so I can bind_column ALL of the variables from both databases in one run.

Can someone show me how that'd be done? An example of my two databases and what I'm trying do to is below.

my $data = qq(SELECT FROM db1 name, age, email);
my $sth = $dbh->prepare($data);
$sth->execute() or die $dbh->errstr;

my $data = qq(SELECT FROM db2 height, weight, shoesize);
my $sth = $dbh->prepare($data);
$sth->execute() or die $dbh->errstr;

my ($name, $age, $email, $height, $weight, $shoesize);
$sth->bind_colums(\$name, \$age, \$email, \$height, \$weight, $shoesiz
+e);

while($sth->fetch)
{
   print "$name is $age and at $email. Stands at $height, sits and $we
+ight and kicks puppies with size $shoesize shows\n";
}
[download]

Comment on 2 mysql selects in one statement Download Code

Replies are listed 'Best First'.
Re: 2 mysql selects in one statement by renodino (Curate) on Dec 11, 2006 at 22:12 UTC
don't toss some advanced techniques my way. I fear you're not going to get very far wo/ some "advanced techniques", assuming you consider JOINs to be advanced. A JOIN is what you're attempting, except you haven't specified any key columns in your queries. Presumably, "name" is the primary key of db1...hopefully, "name" exists as a foreign key in db2. In which case your 2 queries get folded into a single joined query: `SELECT db1.name, db1.age, db1.email, db2.height, db2.weight, db2.shoes +ize FROM db1 JOIN db2 on (db1.name = db2.name)` [download] (or somesuch JOIN syntax; I leave the details to you and your MySQL docs to figure that bit out.) fixed typo in my JOIN: "JOIN db1" should be "JOIN db2" Perl Contrarian & SQL fanboy	[reply] [d/l]
Re^2: 2 mysql selects in one statement by sulfericacid (Deacon) on Dec 12, 2006 at 16:33 UTC
I'm not exactly sure JOIN is the way to go. I'd use `SELECT db1.name, db1.age, db1.email, db2.height, db2.weight, db2.shoes +ize FROM db1, dbi WHERE db1.name = db2.name AND other="test"` [download] "Age is nothing more than an inaccurate number bestowed upon us at birth as just another means for others to judge and classify us" sulfericacid	[reply] [d/l]
Re: 2 mysql selects in one statement by chromatic (Archbishop) on Dec 11, 2006 at 22:10 UTC
You need to `JOIN` the tables together somehow. What's their relationship? How do you know that a particular row in one table corresponds to a row in the other table? Without knowing that, it's likely that your information will be incorrect in subtle ways. Once you answer that question, it'll be easier to demonstrate how to `SELECT` from multiple tables.	[reply] [d/l] [select]
Re: 2 mysql selects in one statement by planetscape (Chancellor) on Dec 12, 2006 at 02:44 UTC
MySQL Crash Course Part One Part Two Part Three ...is a great place to start. Part Three introduces simple JOINs. HTH, planetscape	[reply]
Re: 2 mysql selects in one statement by CountZero (Bishop) on Dec 12, 2006 at 00:18 UTC
It would really help if you would brush up on your SQL-skills, because even those simple SQL-statements you wrote aren't valid SQL at all. SQL-course isn't a bad place to start. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]
Re: 2 mysql selects in one statement by Devanchya (Beadle) on Dec 12, 2006 at 01:43 UTC
As mentioned, Join is the way to go. Also I hate seeing people bind their data this way with MySQL. It just means trouble if you suddenly decide to mess with the SQL statement. checkout fetchrow_hashref. This is your friend. -- Even smart people are dumb in most things...	[reply]
Re: 2 mysql selects in one statement by jbert (Priest) on Dec 12, 2006 at 09:32 UTC
For learning a bit more about SQL, I'm a big fan of Philip Greenspun's SQL for Web Nerds. It may be written in terms of Oracle, but the earlier chapters are fairly generic across different SQL dialects and I found it very well written. The chapter which covers simple queries up to joins is here.	[reply]
Re: 2 mysql selects in one statement by robot_tourist (Hermit) on Dec 12, 2006 at 12:31 UTC
I learned the quick and dirty way, so I tend to do implicit joins with some fancy (or ugly, depending on your viewpoint) where clause stuff. Here's one I made earlier with 4 tables that gets all the units in the database that have a certain $datavalue. IDs are primary keys in their own table that also exist as fields in other tables, follow the pattern of the tables and IDs for the relationship between the tables (all names munged as they wouldn't mean anything outside my project anyway)... `SELECT field1, field2, field3, field4, FROM table1, table2, table3, table4 WHERE (table1.ID_1 = table2.ID_1) AND (table2.ID_2 = table3.ID_2) AND (table3.ID_3 = table4.ID_3) AND (table3.ID_4 = $data_value);` [download] It works for me, if the more experienced SQLMonks say its bad, don't do it. How can you feel when you're made of steel? I am made of steel. I am the Robot Tourist. Robot Tourist, by Ten Benson Edited: took out extraneous ' characters (my excuse is I pasted from Delphi, where you don't get automatic multiline strings)	[reply] [d/l]


Welcome to the Monastery
	PerlMonks