PostgreSQL JOIN data from 3 tables

Question

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...).

Answer 1

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

Answer 2

Maybe the following is what you are looking for:

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