Postgresql - return results if field value exists in another table

Question

I have the following postgresql query which selects a few fields from a table called statement_bank:

SELECT statement_id, statement_value FROM statement_bank WHERE category_id=6 AND level=7

I have another table called statement_records, and the fields for that table are:

date | user_id | statement_id

Once a statement is used for a particular user_id, the statement_id is recorded in the statement_records table. This may be recorded a few times for different users.

I am looking to run the first query so that it only returns the statement_id's from the statement_bank table if it exists in the statement_records table between a specific date e.g. between 01-01-2013 AND 01-01-2014

Any suggestions on how I can achieve this?

Thanks

What about using a JOIN ?

mreiterer
– mreiterer

2014-10-10 08:19:43 +00:00
Commented Oct 10, 2014 at 8:19 — mreiterer
– mreiterer, Commented Oct 10, 2014 at 8:19

Gordon Linoff · Accepted Answer · 2014-10-10 08:25:31Z

2

I think you just want an exists clause. Based on the information you provided, it would look something like:

SELECT statement_id, statement_value
FROM statement_bank sr
WHERE category_id=6 AND level=7 and
      exists (select 1
              from statement_record sr
              where sr.user_id = sb.user_id and
                    sr.statement_id = sb.statement_id and
                    sr.date between '2013-01-01' and '2013-12-31'
             );

Note: I am assuming you don't really want matches on 2014-01-01, but only through the end of 2013.

answered Oct 10, 2014 at 8:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

John Over a year ago

Thanks for that Gordon. Where sr.user_id=sb.user_id, i do not have user_id in the statement_bank table, the table just holds unique statements which can then be applied to number of users, hence the reason the statement_id may exist multiple times in the statement_record table. Should i just remove the line that reads sr.user_id = sb.user_id? will duplicate existence of the statement_id in the statement_records table cause a problem then?

Collectives™ on Stack Overflow

Postgresql - return results if field value exists in another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related