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

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

CodePudding user response：

try this :

WITH list AS
(
SELECT n.Notification, n.data1, n.deviceID, first_value(p.Photo) OVER (ORDER BY p.Photo ASC) AS Photo
     , p.data2, p.deviceID
  FROM Notification AS n
 INNER JOIN Photo AS p
    ON p.deviceID = n.deviceID
   AND p.Photo >= n.Notification
 GROUP BY n.Notification, n.data1, n.deviceID
)
SELECT l.Notification, l.data1, l.Photo, p.data2 AS data, l.deviceID
  FROM list AS l
 INNER JOIN Photo AS p
    ON l.deviceID = p.deviceID
   AND l.Photo = p.Photo

CodePudding user response：

You can use the distinct on to get just a single Notification Photo. The problem being that id1 in Notification is not unique (typo?). Note that sorting the age function gives the minimum time between the Notification and the Photo. (See demo)

select distinct on (n.id1,n.deviceid) 
       n.ts       "Notification Time" 
     , n.data1    "Notification Data"
     , p.ts       "Photo Time" 
     , p.data2    "Photo Data" 
     , n.deviceid "Device Id"      
  from notifications  n
  left join photos    p
         on (     p.deviceid = n.deviceid
              and p.ts > n.ts
            )      
 order by n.id1,n.deviceid,age(p.ts,n.ts);