0

How can I make postgreSQL query like this:

SELECT event_id, user_id FROM public."point" 
WHERE user_id = (SELECT id FROM public."user" 
WHERE email='test@gmail.com')

with JOINstatement and without nested SELECT statement. Above works but I think it is not optimal. Thanks for your answers.

MattCodes
  • 489
  • 8
  • 21

1 Answers1

1

For your particular case, this should work:

SELECT p.event_id, p.user_id
FROM public."point" p JOIN
     public."user" u
     ON p.user_id = u.id 
WHERE u.email = 'test@gmail.com';

In general, when switching between JOIN and IN, you need to be careful about duplicates. So the general solution would be:

SELECT p.event_id, p.user_id
FROM public."point" p JOIN
     (SELECT DISTINCT u.id
      FROM public."user" u
      WHERE u.email = 'test@gmail.com'
     ) u
     ON p.user_id = u.id ;

But the id is probably already unique in user.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes this is ti, it works fine. `id` in `user` is unique. I didn't add `email` comparison with `WHERE` in the last part of the query. (... `ON p.user_id = u.id WHERE u.email = 'test@gmail.com'`). Thank you very much! – MattCodes Mar 10 '17 at 21:39