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
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';
Source: Stackoverflow.com