0

I have three tables
parent table: I_MOBILE_USERS

  • IMU_ID (PK)
  • IMU_NAME
  • IMU_SURNAME
  • IMU_CELL_NO
  • and more but they are not needed for the query

Child table 1: I_MOBILE_USER_LOCATIONS

  • IMUS_ID (PK)
  • IMU_ID(FK REFERENCING PARENT TABLE PK)
  • IMUS_LATITUDE (Updates every 5 min saving the persons location)
  • IMUS_LONGITUDE (Updates every 5 min saving the persons location)
  • DATE_CREATED (DEFAULT SYSDATE the date and time of the location log)

Child table 2: I_MOBILE_USER_SOS

  • IMUS_ID (PK)
  • IMU_ID (FK REFERENCING PARENT TABLE PK)
  • IMU_DATE_SOS_CREATED (DEFAULT SYSDATE the date and time the sos was triggered)
  • IMU_DATE_SOS_CLEARED (DISPLAYS THE DATE AND TIME THE SOS WAS CLEARED)

I need a query that would pull the name, surname, cell no and newest record of the persons location but only when the DATE_SOS_CLEARED column is empty and that one should also just get the last sos called by the person

This is what I've tried so far

SELECT  I_MOBILE_USERS.IMU_NAME,
        I_MOBILE_USERS.IMU_SURNAME,
        I_MOBILE_USERS.IMU_CELL_NO,
        I_MOBILE_USER_LOCATIONS.IMUL_LATITUDE,
        I_MOBILE_USER_LOCATIONS.IMUL_LONGITUDE,
        I_MOBILE_USER_SOS.IMUS_ID
        FROM I_MOBILE_USERS
        LEFT OUTER JOIN I_MOBILE_USER_LOCATIONS
        ON I_MOBILE_USERS.IMU_ID = I_MOBILE_USER_LOCATIONS.IMU_ID
        LEFT OUTER JOIN I_MOBILE_USER_SOS 
        ON I_MOBILE_USERS.IMU_ID = I_MOBILE_USER_SOS.IMU_ID
        where I_MOBILE_USER_SOS.IMU_DATE_SOS CLEARED

That does not work because it shows all the records. here is an example of my records:

IMU_ID, IMU_NAME, IMU_SURNAME, IMU_CELL_NO

1 , Devin , Gericke , 0123456789

2 , Jan , Koen , 0124356789

3 , Pieter , Du plooy , 0125346789

IMUL_ID, IMU_ID, IMUL_LATITUDE, IMUL_LONGITUDE

1 , 1 , -26.151674 , 28.1688

2 , 1 , -26.151674 , 28.1688

3 , 2 , -26.151674 , 28.1688

4 , 2 , -26.151674 , 28.1688

5 , 3 , -26.151674 , 28.1688

6 , 3 , -26.151674 , 28.1688

IMUS_ID, IMUL_ID, DATE_SOS_RAISED, DATE_SOS_CLEARED

1, 1 , 1/29/2021 7:32:54 AM, 1/29/2021 7:32:54 AM

2, 1 , 1/29/2021 7:32:54 AM, (Null)

3, 2 , 1/29/2021 7:32:54 AM, 1/29/2021 7:32:54 AM

4, 2, 1/29/2021 7:32:54 AM, (null)

5, 3, 1/29/2021 7:32:54 AM, 1/29/2021 7:32:54 AM

6, 3, 1/29/2021 7:32:54 AM, (null)

Here is an example of how i want it to be displayed

I_MOBILE_USERS.IMU_NAME, I_MOBILE_USERS.IMU_SURNAME, I_MOBILE_USERS.IMU_CELL_NO, I_MOBILE_USER_LOCATIONS.IMUL_LATITUDE, I_MOBILE_USER_LOCATIONS.IMUL_LONGITUDE

Devin, Gericke, 0123456789, -26.151674 , 28.1688

Jan, Koen, 0123456789, -26.151674 , 28.1688

Pieter, Du plooy, 0125346789, -26.151674 , 28.1688

But it should only be displayed when the date_sos_cleared column is null and it should only show one row per person and contain there last know location

Devin Gericke
  • 3
  • 2

0 Answers0