Vortico Vortico - 9 months ago 17
SQL Question

Use WHERE clause on a column from another table

I have this schema.

create table "user" (id serial primary key, name text unique);
create table document (owner integer references "user", ...);

I want to select all the documents owned by the user named "vortico". Can I do it in one query? The following doesn't seem to work.

select * from document where owner.name = 'vortico';

SELECT * FROM document d INNER JOIN "user" u ON d.owner = u.name 
WHERE u.name = 'vortico'