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

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]

Replies are listed 'Best First'.
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]

      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]
        mine works just only not getting the rest columns is the problem

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

        poj
[reply]
[reply]

In Section Seekers of Perl Wisdom