Re^3: sql join with multiple select

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.


#!/usr/bin/perl
use strict;
use DBI;
use Template;

my $template = Template->new;
my $DBH = get_dbh(); # connect somehow
my $janedeo_id = 2;

my $sth = $DBH->prepare('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');
  
$sth->execute($janedeo_id);
my $data = $sth->fetchall_arrayref({});

my $templ =<< "END_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.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>
</html>

END_HTML

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

poj

Comment on Re^3: sql join with multiple select Select or Download Code

Replies are listed 'Best First'.
Re^4: sql join with multiple select by bigup401 (Pilgrim) on Oct 04, 2018 at 14:01 UTC
errors. when i try ur example the script dies all. but mine works just only not getting the rest columns is the problem. but works and prints all info very well `Can't use an undefined value as an ARRAY reference at C:/xampp/perl/ve +ndor/lib/DBI.pm line 2047.` [download]	[reply] [d/l]
Re^5: sql join with multiple select by poj (Abbot) on Oct 04, 2018 at 14:28 UTC
mine works just only not getting the rest columns is the problem Ok, copy/paste the code you have that works here poj	[reply]
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]
Re^7: sql join with multiple select by poj (Abbot) on Oct 04, 2018 at 17:00 UTC
Re^8: sql join with multiple select by bigup401 (Pilgrim) on Oct 04, 2018 at 17:08 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
going round in circles...	[reply]
Re^6: sql join with multiple select by bigup401 (Pilgrim) on Oct 04, 2018 at 14:49 UTC
this works as i told. the problem is when i added some column to table. so cant select other added column from table msg 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^7: sql join with multiple select by poj (Abbot) on Oct 04, 2018 at 15:22 UTC
Re^8: sql join with multiple select by bigup401 (Pilgrim) on Oct 04, 2018 at 15:27 UTC
Some notes below your chosen depth have not been shown here