CODEWITHSUNDEEP
mysql
suchislife
suchislife

Reputation: 1

MySQL Subquery to get username in different table from user id in current query. How?

The following query is the most complex I have been able to learn so far. It calculates SIGN-IN durations from tbl_user_time_log.

SELECT USER_ID,
       UTL_DTSTAMP,
       UTL_TASK,
       SUM(UTL_DURATION) AS UTL_DURATION_TOTAL       
  FROM (

SELECT A.PK_USER_TIME_LOG_ID,
       A.CLIENT_ID,
       A.PROJECT_ID,
       A.USER_ID,
       A.UTL_DTSTAMP,
       /* DATE_FORMAT(A.UTL_DTSTAMP,'%H:%i:%s') AS UTL_DTSTAMP, */
       A.UTL_LATITUDE,
       A.UTL_LONGITUDE,
       A.UTL_EVENT,
       A.UTL_TASK,
       /* DURATION in seconds  */
       TIMESTAMPDIFF(SECOND, A.UTL_DTSTAMP, B.UTL_DTSTAMP) AS UTL_DURATION
FROM   tbl_user_time_log A
       INNER JOIN tbl_user_time_log B
               ON B.PK_USER_TIME_LOG_ID = ( A.PK_USER_TIME_LOG_ID + 1 )
WHERE  A.USER_ID = '465615'
       /* Between current pay period Start date and Current pay period end date */
       /* First day of the week is Monday.*/

       AND ( A.UTL_DTSTAMP BETWEEN '2019-02-24' AND '2019-03-04' )
       /* Filter out Clock Out. */
       AND A.UTL_EVENT <> 'CLOCK OUT'

  /* Our derived table... */
) AS tbl_derived_1

GROUP BY USER_ID, UTL_TASK;

...which returns:

query1

How do I use the same query but for each user id it selects, display the corresponding user first name and user last name from tbl_user?

tbl_user

tbl_user

Upvotes: 0

Views: 309

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

Assuming you have a table user that contain firstname and lastname related by id to tbl_user_time_log you could use a join 

SELECT USER_ID,
       u.firstname, 
       u.lastname,
       UTL_DTSTAMP,
       UTL_TASK,
       SUM(UTL_DURATION) AS UTL_DURATION_TOTAL       
  FROM (

SELECT A.PK_USER_TIME_LOG_ID,
       A.CLIENT_ID,
       A.PROJECT_ID,
       A.USER_ID,
       A.UTL_DTSTAMP,
       /* DATE_FORMAT(A.UTL_DTSTAMP,'%H:%i:%s') AS UTL_DTSTAMP, */
       A.UTL_LATITUDE,
       A.UTL_LONGITUDE,
       A.UTL_EVENT,
       A.UTL_TASK,
       /* DURATION in seconds  */
       TIMESTAMPDIFF(SECOND, A.UTL_DTSTAMP, B.UTL_DTSTAMP) AS UTL_DURATION
FROM   tbl_user_time_log A
       INNER JOIN tbl_user_time_log B
               ON B.PK_USER_TIME_LOG_ID = ( A.PK_USER_TIME_LOG_ID + 1 )
WHERE  A.USER_ID = '465615'
       /* Between current pay period Start date and Current pay period end date */
       /* First day of the week is Monday.*/

       AND ( A.UTL_DTSTAMP BETWEEN '2019-02-24' AND '2019-03-04' )
       /* Filter out Clock Out. */
       AND A.UTL_EVENT <> 'CLOCK OUT'

  /* Our derived table... */
) AS tbl_derived_1
INNER JOIN user u ON u.id = tbl_derived_1.USER_ID

GROUP BY USER_ID, UTL_TASK;

Upvotes: 3

Related Questions