Re^4: output results from sql join

am still not getting the info, am getting blank resluts i want to get

msg from userid(1) names and userid. where people all sent me msg to my userid(2) and have not read them. read is the received column

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;

my $data = $DBH->prepare("SELECT
  M.userid,
  SND.firstname as snd_FNAME,
  SND.lastname as snd_LNAME
from
  msg as M
  join users as SND on M.userid = SND.userid WHERE M.msg_to = ? NOT IN
+ (SELECT RECEIVED 
                       FROM   msg");
$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]

Comment on Re^4: output results from sql join Download Code

Replies are listed 'Best First'.
Re^5: output results from sql join by poj (Abbot) on Sep 29, 2018 at 12:03 UTC
SELECT M.userid, `userid` is a field in table `users` not in `msg`. Try `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` [download] poj	[reply] [d/l] [select]
Re^6: output results from sql join by bigup401 (Pilgrim) on Sep 29, 2018 at 13:09 UTC
it has worked now, thanks poj and roboticus	[reply]