title: Creating an sql view using an inner join statement
body: <p>I am trying to create a SQL view that gets information from 2 tables using an inner join statement but I keep getting an error that I can't figure out. The view statement that I am trying to create takes the first name, last name, and then the pid(whats used to link the tables) and then only displays the people that have a body weight of over 140 pounds. I keep getting an error when I try running my sql file in psql. The error that I get is </p>
  
  <pre><code>\i letsdoit.sql
  output #1
  psql:letsdoit.sql:7: ERROR:  column reference "pid" is ambiguous
  LINE 2: SELECT pid,fname, lnam
  </code></pre>
  
  <p>the code that I have is </p>
  
  <pre><code> \echo output #1
  CREATE VIEW weight AS
  SELECT a.pid, a.fname, a.lname
  FROM letsdoit.person as a
  INNER JOIN letsdoit.body_composition as b
  ON a.pid = b.pid
  WHERE (b.weight&gt;140);
  </code></pre>
  
  <p>and the two tables that I am using are </p>
  
  <pre><code>                                  Table "letsdoit.person"
   Column |         Type          |                      Modifiers                      
   --------+-----------------------+---------------------------------------------------
   pid    | integer               | not null default nextval('person_pid_seq'::regclass)
   uid    | integer               | 
   fname  | character varying(25) | not null
   lname  | character varying(25) | not null
   Indexes
  "person_pkey" PRIMARY KEY, btree (pid)
   Foreign-key constraints:
  "person_uid_fkey" FOREIGN KEY (uid) REFERENCES university(uid) ON DELETE CASCADE
  Referenced by:
  TABLE "body_composition" CONSTRAINT "body_composition_pid_fkey" FOREIGN KEY (pid
  ) REFERENCES person(pid) ON DELETE CASCADE
  TABLE "participated_in" CONSTRAINT "participated_in_pid_fkey" FOREIGN KEY (pid) 
  REFERENCES person(pid)
  </code></pre>
  
  <p>AND</p>
  
  <pre><code>Table "letsdoit.body_composition"
  Column |  Type   | Modifiers 
  --------+---------+-----------
  pid    | integer | not null
  height | integer | not null
  weight | integer | not null
  age    | integer | not null
  Indexes:
  "body_composition_pkey" PRIMARY KEY, btree (pid)
  Foreign-key constraints:
  "body_composition_pid_fkey" FOREIGN KEY (pid) REFERENCES person(pid) ON DELETE CASCADE
  </code></pre>