SQL Create view statement using WITH keyword -

SQL Create view statement using WITH keyword -

i having issues writing create view statement in sql. want personid, first name, , lastly name table people go university of colorado (uid = 2). want utilize clause combine table body_composition table , print out in body_composition table well. here exact definition of query making.

first, write query returns person’s id (pid), first name (fname) , lastly name (lname) people people go university of colorado. then, place query in clause , utilize mutual table look (cte) combine result body composition table via inner bring together body compositions people attend university of colorado.

i seek , run create view statement error

error: syntax error @ or near "what" line 7: what.body_composition c

here code create view statement along tables using.

create view withclause select a.pid, a.fname, a.lname what.person inner bring together what.university b on a.uid = b.uid uid = 2 what.body_composition c select * what.body_composition;

here 3 tables using

table "what.university" column | type | modifiers -----------------+-----------------------+-------------------------------------- uid | integer | not null default nextval('university_uid_seq'::regclass) university_name | character varying(50) | city | character varying(50) | table "what.body_composition" column | type | modifiers --------+---------+----------- pid | integer | not null height | integer | not null weight | integer | not null age | integer | not null table "what.person" column | type | modifiers --------+-----------------------+----------------------------------------------- pid | integer | not null default nextval('person_pid_seq'::reg class) uid | integer | fname | character varying(25) | not null lname | character varying(25) | not null

based on problem description i'm pretty sure want:

create view withclause cte ( select p.pid, p.fname, p.lname what.person p inner bring together what.university u on p.uid = u.uid p.uid = 2 ) select cte.pid, cte.fname, cte.lname, c.age, c.height, c.weight cte inner bring together what.body_composition c on c.pid = cte.pid;

sample sql fiddle (based on postgres i'm assuming you're using based on psql tag).

sql view psql create-view