Creating an sql view using an inner join statement

Question

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

\i letsdoit.sql
output #1
psql:letsdoit.sql:7: ERROR:  column reference "pid" is ambiguous
LINE 2: SELECT pid,fname, lnam

the code that I have is

 \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>140);

and the two tables that I am using are

                                  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)

AND

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

score 1 · Accepted Answer · answered Oct 05 '14 at 02:00

1

You need to specify which pid you are looking for!

replace like this:

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>140);

answered Oct 05 '14 at 02:00

baao

71,625
17
143
203

@micheal Thanks, I still get one error though for some reason, I updated my code in the original post. But the error I get is => \i letsdoit.sql output #1 psql:letsdoit.sql:7: ERROR: relation "weight" already exists – disciples22 Oct 05 '14 at 02:06
Do you have any idea why this is? – disciples22 Oct 05 '14 at 02:14
Its on line 7. The error is psql:letsdoit.sql:7: ERROR: relation "weight" already exists – – disciples22 Oct 05 '14 at 02:16
It might have to do something with the primary and foriegn keys? – disciples22 Oct 05 '14 at 02:17
I don't think so, but can only guess. Can you please try it like this: WHERE b.weight > 140 // Without () – baao Oct 05 '14 at 02:23
This might help you: http://stackoverflow.com/questions/8792912/postgresql-error-relation-already-exists – baao Oct 05 '14 at 02:30
I tried it without the () but I still get the same error. Do you have any ideas? – disciples22 Oct 05 '14 at 02:33
Sorry, no good ideas here. Do you have another view or table called weight? Did you try changing the name of the view? Sorry, but I can't see the error from here. – baao Oct 05 '14 at 02:37
I think its because the view is called weight and the column is called weight – disciples22 Oct 05 '14 at 02:38
What happens when you change the views name? – baao Oct 05 '14 at 02:39

Creating an sql view using an inner join statement

1 Answers1