CODEWITHSUNDEEP
sqlpostgresql
Vortico
Vortico

Reputation: 2749

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';

Upvotes: 1

Views: 10317

Answers (3)

newman
newman

Reputation: 2719

You can use subquery. For your example it can be faster

SELECT * FROM document WHERE 
  owner = (SELECT id FROM users WHERE name = 'vortico');

Upvotes: 2

KwstasMost
KwstasMost

Reputation: 130

i think you need to join the tables you need . You can do this with inner join ,left joins depending on what you want to appear. i dont know your schema exactly so here is a link on how inner join works.Hope this helps! http://www.w3schools.com/sql/sql_join.asp http://www.w3schools.com/sql/sql_join_inner.asp

Upvotes: 1

stan
stan

Reputation: 4995

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

Upvotes: 5

Related Questions