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

baao · Accepted Answer · 2014-10-05 02:00:42Z

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 5, 2014 at 2:00

baao

73.5k18 gold badges152 silver badges208 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

disciples22 Over a year ago

@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 Over a year ago

Do you have any idea why this is?

disciples22 Over a year ago

Its on line 7. The error is psql:letsdoit.sql:7: ERROR: relation "weight" already exists –

disciples22 Over a year ago

It might have to do something with the primary and foriegn keys?

baao Over a year ago

I don't think so, but can only guess. Can you please try it like this: WHERE b.weight > 140 // Without ()

|

Collectives™ on Stack Overflow

Creating an sql view using an inner join statement

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related