vendredi 8 mai 2015

SQL: Searching X table and returning rows from Y based on X data

  1. Search goals for rows where userid = 1.
  2. Using returned rows, search checkin where biometricid's match, along with the userid, and filter out rows that are older than the goal date.

Note: Both the userid and biometricid are foreign tables.

How may I do this with one query?

checkin
id  | userid    | date                  | biometricid   | value
1   | 1         | 2015-01-10 00:00:00   | 1             | 9
2   | 1         | 2000-05-11 00:00:00   | 1             | 7
3   | 2         | 2015-01-10 00:00:00   | 1             | 9
4   | 1         | 2015-01-10 00:00:00   | 2             | 1
5   | 1         | 2017-01-11 00:00:00   | 1             | 4

goals
id  | userid | date                 | biometricid   | value 
1   | 1      | 2000-01-05 00:00:00  | 1             | 3
2   | 1      | 2015-01-01 00:00:00  | 2             | 2
3   | 2      | 2015-01-01 00:00:00  | 1             | 2

desired result
id  | date                  | biometricid   | value 
1   | 2015-01-10 00:00:00   | 1             | 9
2   | 2017-01-11 00:00:00   | 1             | 4

Aucun commentaire:

Enregistrer un commentaire