Sql Create View Statement Using With Keyword

I am having issues writing this Create view statement in SQL. I want to get personID, first name, and last name from a table for people that go to the University of Colorado (uid =

Solution 1:

Based on the problem description I'm pretty sure this is what you want:

CREATE VIEW withclause ASWITH cte AS (
  SELECT p.pid, p.fname, p.lname
  FROM what.person as p
  INNER JOIN what.university as u
  ON p.uid = u.uid
  WHERE p.uid = 2
)

SELECT cte.pid, cte.fname, cte.lname, c.age, c.height, c.weight
FROM cte
INNER JOIN what.body_composition c on c.pid = cte.pid;

Sample SQL Fiddle (based on Postgres which I'm assuming you're using based on the psql tag).