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

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]

Replies are listed 'Best First'.
Re^7: sql join with multiple select
by poj (Abbot) on Oct 04, 2018 at 17:00 UTC

    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]

        It looks like the table alias names SND and M are confusing you so remove them.
        If this is now your tables

        TABLE users
userid   firstname    lastname
1         JOHN         DEO
2         JANE         DEO

TABLE msg
msg_id    msg_from     msg_to      received   age  city  country

        [download]

        The correct SQL (without alias names) is

        SELECT users.userid, users.firstname, users.lastname, msg.age, msg.cit
+y, msg.country FROM msg
JOIN users ON users.userid = msg.msg_from
WHERE msg.msg_to = ? 
  AND msg.received IS NULL

        [download]
        poj
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom