[postgresql] PostgreSQL JOIN data from 3 tables

I'm new to PostgreSQL and trying to get a query written. I'm pretty sure it's easy for someone who knows what they are doing - I just don't! :)

Basically I have three tables. In the first, I store details about patients. In the second, I store a reference to each image of them. In the third, I store the link to the file path for the image. I didn't design the database, so I'm not sure why the image files table is separated, but it is.

What I want to be able to do is select data from the first table, joining in data from a second then third table so I end up with the name & file path in the result.

So the basic structure is:

Table 1:
person_id | name

Table 2:
person_id | image_id

Table 3:
image_id | `path filename`

What I want to do is in one query, grab the person's 'name' and the image 'path filename'.

I'm happy with a "template" style answer with the join I need. I don't need it to be written in actual code. (i.e. I'm thinking you can just write me an answer that says SELECT table1.name, table3.pathfilename FROM JOIN ... etc...).

This question is related to postgresql

The answer is


Something like:

select t1.name, t2.image_id, t3.path
from table1 t1 inner join table2 t2 on t1.person_id = t2.person_id
inner join table3 t3 on t2.image_id=t3.image_id

Maybe the following is what you are looking for:

SELECT name, pathfilename
  FROM table1
  NATURAL JOIN table2
  NATURAL JOIN table3
  WHERE name = 'John';