CODEWITHSUNDEEP
postgresqlpsycopg2
Brosef
Brosef

Reputation: 3095

Query two tables with one to many relationship

I'm using two Postgres tables that have a one to many relationship. The primary table is called users and the other table is called files.

The users table has the following columns:

id SERIAL PRIMARY KEY,
email VARCHAR(128) NOT NULL,
username VARCHAR(128) UNIQUE NOT NULL,
password_hash VARCHAR(128) NOT NULL

The files table has the following columns:

id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
title VARCHAR(128) NOT NULL,
url  VARCHAR(128) NOT NULL

When I log into my app, I'm querying all the files to display by doing 

cur.execute('SELECT * from files')

and when I want a specific users files I run

cur.execute('SELECT * from files where user_id = %i' % user_id)

For my query that fetches all the files, I'd like to adjust it so that I get the username associated with each file also. How should I tailor my execute statement to make that happen?

Upvotes: 0

Views: 177

Answers (1)

JimmyB
JimmyB

Reputation: 195

Try the following. I know this syntax would work with other dbms':

cur.execute('SELECT f.*, u.username from files as f, users as u where u.id = f.user_Id)

Upvotes: 1

Related Questions