sql join with multiple select

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

this works

TABLE users

userid FNAME LNAME 
1      JOHN  DEO
2      JANE  DEO

TABLE MSG

msg_id    msg_from     msg_to      received  
1         userid(1)    userid(2)   null       


$janedeo_id = 2;

  
  
  
  $janedeo_id = 2;

my $data = $DBH->prepare("SELECT SND.userid, SND.firstname, SND.lastna
+me
FROM msg as M
JOIN users as SND 
  ON SND.userid = M.msg_from
WHERE M.msg_to = ? 
  AND M.received iS NULL");
$data->execute($janedeo_id);
$datainfo = $data->fetchall_arrayref();
while (my $row = $data->fetchrow_hashref) {
    foreach $row ( @$data) {
        ($userid, $snd_FNAME, $snd_LNAME) = @$data;
}
}
my $templ    = <<START_HTML;
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1
+/>
<title>Untitled Document</title>
</head>

<body>
[% FOREACH name IN list %]
<p>userid    [% name.0 %] </p>
<p>firstname [% name.1 %] </p>
<p>lastname  [% name.2 %] </p>
[% END %]
</body>
</html>

START_HTML

$template->process (\$templ, { list => \@$data })
        or die $template->error;
[download]

now my problem is here. i added title, age and country to table MSG. so i want to output them also

TABLE users

userid FNAME LNAME 
1      JOHN  DEO
2      JANE  DEO

TABLE MSG

msg_id    msg_from     msg_to      received  title    age     country 
1         userid(1)    userid(2)   null       mr      26      anywhere


$janedeo_id = 2;

  
  
  
  $janedeo_id = 2;

my $data = $DBH->prepare("SELECT SND.userid, SND.firstname, SND.lastna
+me
FROM msg as M
JOIN users as SND 
  ON SND.userid = M.msg_from
WHERE M.msg_to = ? 
  AND M.received iS NULL IN (SELECT title, age, country FROM MSG)");
$data->execute($janedeo_id);
$datainfo = $data->fetchall_arrayref();
while (my $row = $data->fetchrow_hashref) {
    foreach $row ( @$data) {
        ($userid, $snd_FNAME, $snd_LNAME, $tile, $age, $country) = @$d
+ata;
}
}
my $templ    = <<START_HTML;
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1
+/>
<title>Untitled Document</title>
</head>

<body>
[% FOREACH name IN list %]
<p>userid    [% name.0 %] </p>
<p>firstname [% name.1 %] </p>
<p>lastname  [% name.2 %] </p>
<p>title     [% name.3 %] </p>
<p>age       [% name.4 %] </p>
<p>country   [% name.5 %] </p>
[% END %]
</body>
</html>

START_HTML

$template->process (\$templ, { list => \@$data })
        or die $template->error;
[download]

Comment on sql join with multiple select Select or Download Code

Replies are listed 'Best First'.
Re: sql join with multiple select by poj (Abbot) on Oct 03, 2018 at 17:37 UTC
this works my $data = $DBH->prepare(…) ' ' $template->process (\$templ, { list => \@$data }) or die $template->error; How can it ? I get the error `"Not an ARRAY reference"` because `$data` is a statement handle. Your array ref is `$datainfo` poj	[reply] [d/l] [select]
Re^2: sql join with multiple select by LanX (Saint) on Oct 03, 2018 at 21:18 UTC
for me this looks like the mix of two different codesnippets. (I'm getting a bit worried, if programs already start to have sex now...) Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery FootballPerl is like chess, only without the dice}	[reply]
Re^2: sql join with multiple select by bigup401 (Pilgrim) on Oct 04, 2018 at 07:59 UTC
its typing error. was tired. but its like this `$data->execute($janedeo_id); $datainfo = $data->fetchall_arrayref(); while (my $row = $data->fetchrow_hashref) { foreach $row ( @$data) { ($userid, $snd_FNAME, $snd_LNAME) = @$data; } }` [download]	[reply] [d/l]
Re^3: sql join with multiple select by poj (Abbot) on Oct 04, 2018 at 12:18 UTC
You can avoid typing errors by copy/pasting from your actual code. If you want to SELECT more fields then add them into the SELECT part, not the WHERE part. This is explained in the SQL tutorial suggested by LanX. Change this `SELECT SND.userid, SND.firstname, SND.lastname FROM msg as M JOIN users as SND ON SND.userid = M.msg_from WHERE M.msg_to = ? AND M.received iS NULL IN (SELECT title, age, country FROM MSG)` [download] to this `SELECT userid, FNAME, LNAME, title, age, country FROM msg as M JOIN users as SND ON SND.userid = M.msg_from WHERE M.msg_to = ? AND M.received iS NULL` [download] But, if title, age and country relate to the user why add them to message table MSG and not the user table ?. Also, age increases over time whereas birth date/year is constant. If required, age can be calculated. As your requirements are increasing this seems a good time to change your data structure from an ArrayOFArrays(AoA) to an ArrayOfHashes(AoH) by adding {} here `my $data = $sth->fetchall_arrayref({});` and changing your template to `<body> [% FOREACH name IN list %] <p>userid [% name.userid %] </p> <p>firstname [% name.FNAME %] </p> <p>lastname [% name.LNAME %] </p> <p>title [% name.title %] </p> <p>age [% name.age %] </p> <p>country [% name.country %] </p> [% END %] </body>` [download] The advantage is a clearer template and the order of fields in the SELECT does not matter. Read more... (1504 Bytes) poj	[reply] [d/l] [select]
Re^4: sql join with multiple select by bigup401 (Pilgrim) on Oct 04, 2018 at 14:01 UTC
Re^5: sql join with multiple select by poj (Abbot) on Oct 04, 2018 at 14:28 UTC
Some notes below your chosen depth have not been shown here
Re^5: sql join with multiple select by marto (Cardinal) on Oct 04, 2018 at 14:32 UTC
Some notes below your chosen depth have not been shown here
Re: sql join with multiple select by AnomalousMonk (Archbishop) on Oct 03, 2018 at 18:51 UTC
bigup401: Further to poj's reply: Also note that if `$data` were an array reference, `$data` and `\@$data` would be exactly the same thing: `c:\@Work\Perl\monks>perl -wMstrict -le "my @ra = (1, 2, 3); my $ar = \@ra; my $ref_deref_ref = \@$ar; print qq{$ar $ref_deref_ref}; ;; $ar == $ref_deref_ref or die 'not same'; " ARRAY(0x145ff0) ARRAY(0x145ff0)` [download] Give a man a fish: `<%-{-{-{-<`	[reply] [d/l] [select]
Re^2: sql join with multiple select by bigup401 (Pilgrim) on Oct 04, 2018 at 08:03 UTC
i dont meet any error in my script. it was just typing error. bt i have corrected it on my reply. the error i have is adding more column to be printed	[reply]
Re: sql join with multiple select by LanX (Saint) on Oct 03, 2018 at 16:34 UTC
two issues > `"SELECT SND.userid, SND.firstname, SND.lastname` ? > `$datainfo = $data->fetchall_arrayref(); while (my $row = $data->fetchrow_hashref) {` [download] it's possible to do a fetchrow after a fetchall ? Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery FootballPerl is like chess, only without the dice}	[reply] [d/l] [select]
Re^2: sql join with multiple select by bigup401 (Pilgrim) on Oct 03, 2018 at 16:49 UTC
am using fetchrow_hashref to fast up resulting	[reply]
Re^3: sql join with multiple select by LanX (Saint) on Oct 03, 2018 at 17:15 UTC
https://www.w3schools.com/sql/ Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery FootballPerl is like chess, only without the dice}	[reply]
Re^4: sql join with multiple select by bigup401 (Pilgrim) on Oct 04, 2018 at 08:00 UTC
Re: sql join with multiple select by AnomalousMonk (Archbishop) on Oct 04, 2018 at 18:07 UTC
I vote ++500 XP to poj just for divinely inspired patience. Give a man a fish: `<%-{-{-{-<`	[reply] [d/l]
Re: sql join with multiple select by Corion (Patriarch) on Oct 05, 2018 at 13:48 UTC
Crossposted to StackOverflow.	[reply]