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:

Table1:
person_id | name

Table2:
person_id | image_id

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

it would be usefull to show us what have you tried already and what kind of research have you completed before you pasted the question. it would highlight the fact you have tried to solve it yourself first. — Dariusz Krynicki
– Dariusz Krynicki, Commented Feb 28, 2018 at 9:26

Gringo Suave · Accepted Answer · 2021-07-28 20:42:23Z

209

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

edited Jul 28, 2021 at 20:42

Gringo Suave

32.3k7 gold badges95 silver badges82 bronze badges

answered Apr 3, 2013 at 22:50

ozczecho

8,9098 gold badges39 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Satyam Over a year ago

Need a small change here: My requirement is like t2.image_id != t3.image_id, but the condition is not working........ anything else that I have to do?

Daniel Frey · Accepted Answer · 2013-04-03 22:56:13Z

25

Maybe the following is what you are looking for:

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

edited Apr 3, 2013 at 22:56

answered Apr 3, 2013 at 22:50

Daniel Frey

57.2k13 gold badges129 silver badges182 bronze badges

3 Comments

Daniel Frey Over a year ago

@digitaljoel I don't see why it's dangerous, especially since the OP seems to have tables where he explicitly chose matching column names. Why not use this to his advantage?

digitaljoel Over a year ago

let's say next month in table1 he adds a column named "last_used" indicating the last time a user logged in. A month after that he also adds a column in table3 named "last_used" which indicates the last time the image was displayed. Wouldn't the natural join now improperly attempt to apply those columns also and suddenly his query is returning much less information than it used to and he can't figure out why because the new columns were added a month apart and 2 months after the original query was written?

John Cleary Over a year ago

Interesting - I didn't know about Natural Joins. I think in this case I'll use the answer above, but handy for a quick & dirty use in future...

Collectives™ on Stack Overflow

PostgreSQL JOIN data from 3 tables

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related