Join unrelated tables(A,B) with some condition on basis of table A for postgresql

I am trying to join two unrelated tables, both table has a common column, a foreign key of third table, If suppose first table is Notification table, and second table is Photos table. A device takes photo on basis of several criteria on timely basis, if any movements, and also on basis of notifications. Need to pick both notifications and only one photo after that notification.

Notification Table
+-----+----------------------------+----------+----------------+
| id1 |  Notification timestamp    |   data1  |    deviceID    |
+-----+----------------------------+----------+----------------+
| 11  |  2020-10-26 22:31:33.602   | 2038.75  |        6       |
| 12  |  2020-10-26 22:31:34.001   | 2043.5   |        2       |
| 13  |  2020-10-26 22:51:47.178   | 1778.75  |        8       |
| 14  |  2020-10-26 23:12:07.761   | 2015.75  |        3       |
| 15  |  2020-10-26 23:12:09.611   | 2005.75  |        2       |
| 15  |  2020-10-26 23:12:09.122   | 1963.25  |        7       |
| 17  |  2020-10-26 23:12:11.930   | 694.75   |        2       |
+-----+----------------------------+----------+----------------+

Photo Table
+-----+---------------------------+----------+----------------+
| id2 |    Photo timestamp        |   data2  |    deviceID    |
+-----+---------------------------+----------+----------------+
| 21  |  2020-10-26 22:31:34.016  |     5    |        2       |
| 22  |  2020-10-26 22:31:34.102  |    75    |        6       |
| 23  |  2020-10-26 22:31:34.022  |    20    |        3       |
| 24  |  2020-10-26 22:51:47.97   |    55    |        2       |
| 25  |  2020-10-26 22:51:47.975  |    63    |        7       |
| 26  |  2020-10-26 22:51:47.977  |    19    |        4       |
| 27  |  2020-10-26 22:51:47.978  |    77    |        8       |
| 28  |  2020-10-26 23:12:07.613  |    44    |        6       |
| 29  |  2020-10-26 23:12:08.61   |    11    |        3       |
| 30  |  2020-10-26 23:12:09.625  |    51    |        2       |
| 31  |  2020-10-26 23:12:09.628  |    63    |        7       |
| 32  |  2020-10-26 23:12:10.635  |    19    |        4       |
| 33  |  2020-10-26 23:12:11.635  |    77    |        8       |
| 34  |  2020-10-26 23:12:12.235  |    44    |        6       |
| 35  |  2020-10-26 23:12:12.435  |    11    |        3       |
| 36  |  2020-10-26 23:12:12.650  |    51    |        2       |
+-----+---------------------------+----------+----------------+

Resultant Table
+---------------------------+----------+---------------------------+----------+----------------+
|  Notification timestamp   |   data1  |    Photo timestamp        |   data   |    deviceID    |
+---------------------------+----------+---------------------------+----------+----------------+
| 2020-10-26 22:31:33.602   | 2038.75  |  2020-10-26 22:31:34.102  |    75    |        6       |
| 2020-10-26 22:31:34.001   | 2043.5   |  2020-10-26 22:31:34.016  |     5    |        2       |
| 2020-10-26 22:51:47.178   | 1778.75  |  2020-10-26 22:51:47.978  |    77    |        8       |
| 2020-10-26 23:12:07.761   | 2015.75  |  2020-10-26 23:12:08.61   |    11    |        3       |
| 2020-10-26 23:12:09.611   | 2005.75  |  2020-10-26 23:12:09.625  |    51    |        2       |
| 2020-10-26 23:12:09.122   | 1963.25  |  2020-10-26 23:12:09.628  |    63    |        7       |
| 2020-10-26 23:12:11.930   | 694.75   |  2020-10-26 23:12:12.650  |    51    |        2       |
+---------------------------+----------+---------------------------+----------+----------------+

Tried with joining sub-queries. Definitely not a correct way to do though

select "notification".*, "filter_data".* FROM public.notification 
  left JOIN
    ( select "photo"."time", "photo"."data2", "photo"."deviceid", "notification"."id" as "eid", "notification"."time" as "etime" 
      from public."photo" inner join  public."notification" on "notification"."deviceid" = "photo"."deviceid" where 
      "photo"."time" >= "notification"."time" order by "photo"."time" ASC
      limit 1 
    )
  filter_data on "filter_data"."did" = "notification"."deviceid" 
  where "notification"."time" > date '2021-10-28 01:37:20.305+00' - interval '7 days'
  order by "notification"."device" ASC, "notification"."time" DESC limit 100;

Kindly suggest. I am aware my solution is not right

from Recent Questions - Stack Overflow https://ift.tt/3GoKsiG
https://ift.tt/eA8V8J