Postgres join query: Most efficient

Question

I have a application where users can take images and tag them selfs. Here is the database structure looks like:

For the purpose of this problem lets take three main table that involve:

user, image, user_image

user table
id, user_name, age
1, test1, 23
2, test2, 34

image table
id, users_in_image, datetime
1, test1,test2, 03/01/2000
2, test1, 03/01/2003

user_image table
id, user_id, image_id
1, 1, 1
2, 1, 2

Every time user take an image it creates a record in image, user and user_image, given a user I want to see all users that user has taken images with and what are those image_ids.

Here is my solution

select * from user_image where image_id
in (select image_id from user_image where user_id = '1') and user_id != '1';

I am worried this is not a good solution. I would like to get some thoughts from experts.

Nope unfortunately not its a comma seperated text db was design like that. — add-semi-colons
– add-semi-colons, Commented Jul 30, 2014 at 0:31
Find whoever designed it and whack them with a cluebat. Gently. — Craig Ringer
– Craig Ringer, Commented Jul 30, 2014 at 0:45
What is your question? What have you tried? Show example results. etc. — Dwayne Towell
– Dwayne Towell, Commented Jul 30, 2014 at 2:30
Your question should probably not be title "most efficient" since you don't even have a solution, at all, yet. — Dwayne Towell
– Dwayne Towell, Commented Jul 30, 2014 at 2:31

Erwin Brandstetter · Accepted Answer · 2014-07-30 21:20:06Z

1

If you actually mean:

"Find all users that are connected to an image that a given user is connected to"

SELECT ui2.user_id
FROM   user_image ui1
JOIN   user_image ui2 USING (image_id)
WHERE  ui1.user_id =  1
AND    ui2.user_id <> 1
GROUP  BY 1;

Join the table to its self.
Or, to get actual users, not just user_id:

SELECT u.*
FROM  (
   SELECT ui2.user_id
   FROM   user_image ui1
   JOIN   user_image ui2 USING (image_id)
   WHERE  ui1.user_id =  1
   AND    ui2.user_id <> 1
   GROUP  BY 1
   ) ui
JOIN  "user" u ON u.id = ui.user_id;

Why the quotes around '1'? You are not storing numbers as text, are you?

Aside: Don't use the reserved word user as identifier.

edited Jul 30, 2014 at 21:20

answered Jul 30, 2014 at 19:16

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

3 Comments

add-semi-colons Over a year ago

What I am trying to do is Given a user, who are the other users this user has taken images with.

add-semi-colons Over a year ago

but why would we use AND ui2.user_id <> 2 to the query only one userId should be passed in. Assume from web point of few user login 1 login...

Erwin Brandstetter Over a year ago

@Null-Hypothesis: Sorry, that was a typo.

Collectives™ on Stack Overflow

Postgres join query: Most efficient

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related