in reply to Re^4: sql join with multiple select
in thread sql join with multiple select

mine works just only not getting the rest columns is the problem

Ok, copy/paste the code you have that works here

poj

Replies are listed 'Best First'.
Re^6: sql join with multiple select
by bigup401 (Pilgrim) on Oct 04, 2018 at 15:06 UTC

    this works. simple and easy

    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 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);
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]
[reply]
[d/l]
Re^6: sql join with multiple select
by bigup401 (Pilgrim) on Oct 04, 2018 at 16:05 UTC

    the problem is not the code. the problem is at sql statement

    coz i have made some tests and found the problem is in sql statement

    this statement works

    my $data = $DBH->prepare("SELECT SND.userid, SND.fname, SND.lname
FROM msg as M
JOIN users as SND 
  ON SND.userid = M.msg_from
WHERE M.msg_to = ? 
  AND M.received iS NULL");

<p>but my problem and question was after adding some column to table m
+sg and printing them out. i get error that tells me cant find such da
+ta from db according to my script. that means cant locate any data in
+ db </p>
<p>my problem is here wen i include other columns to be selected. the 
+statement dies and cant print anything just getting error nothing to 
+print from db</p>
<code>
my $data = $DBH->prepare("SELECT SND.userid, SND.fname, SND.lname, SND
+.city, SND.age, SND.city 
FROM msg as M
JOIN users as SND 
  ON SND.userid = M.msg_from
WHERE M.msg_to = ? 
  AND M.received iS NULL");

    [download]
[reply]
[d/l]

      In your original post the MSG table with added fields was

      msg_id    msg_from     msg_to      received  title    age     country

      but now your sql select includes city twice. What columns are you trying to add. ? 

      SELECT SND.userid, SND.fname, SND.lname, SND.city, SND.age, SND.city
      poj
[reply]

        sorry i mixed city with country and also forget to include title

        but this is the MSG Table and thats wat i want to select 

        SELECT SND.userid, SND.fname, SND.lname, SND.title, SND.age, SND.count
+ry

        [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom