comment on

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

In reply to Re^3: sql join with multiple select by poj
in thread sql join with multiple select by bigup401

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.