CODEWITHSUNDEEP
sqlpostgresqlselect
John
John

Reputation: 7067

Postgresql - return results if field value exists in another table

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

Upvotes: 0

Views: 2901

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 2

Related Questions