how to query from multiple tables and get the column value from the other table-CodePudding

Problem: I need to get the username based on UnitNo on unitlog_table.

This is my query:

select distinct(avh.UnitNo), 
    isnull((select count(UnitNo) from unitlog_table 
            where UnitNo = avh.UnitNo 
              and timestamp between '2021-09-01 00:00:00.00' and '2021-09-01 08:00:00.00' 
              and speed >= 41),0) as overord,
    isnull((select count(UnitNo) from unitlog_table 
            where UnitNo = avh.UnitNo 
              and timestamp between '2021-09-01 00:00:00.00' and '2021-09-01 08:00:00.00' 
              and speed >= 71),0) as overex ,
    isnull((select count(UnitNo) from unitlog_table 
            where UnitNo = avh.UnitNo 
              and timestamp between '2021-09-01 00:00:00.00' and '2021-09-01 08:00:00.00' 
              and speed >= 91),0) as overc,
    isnull((select count(UnitNo) from unitlog_table 
            where UnitNo = avh.UnitNo 
              and timestamp between '2021-09-01 00:00:00.00' and '2021-09-01 08:00:00.00' 
              and speed >= 41 and speed >= 71 and speed >= 91), 0) as ttover,
    (select distinct(username) 
     from unit_table av 
     inner join users_table ut on ut.UnitNo = av.UnitNo 
     where UnitNo = avh.UnitNo) as username
from 
    unitlog_table avh 
where 
    avh.UnitNo in (select UnitNo from unit_table where userid = '1122')  
    and timestamp between '2021-09-01 00:00:00.00' and '2021-09-01 08:00:00.00'

This is my table 1 (unitlog_table):

UnitNo        timestamp           speed
----------------------------------------
 unit1   2021-09-01 07:36:21.00    7.72
 unit1   2021-09-01 07:36:30.00    9.58
 unit1   2021-09-01 07:37:16.00    7.2
 unit1   2021-09-01 07:37:37.00    18.32
 unit1   2021-09-01 07:38:18.00    1.85
 unit1   2021-09-01 07:38:27.00    13.32
 unit1   2021-09-01 07:38:45.00    12.94
 unit1   2021-09-01 07:47:39.00    8.34
 unit1   2021-09-01 07:48:07.00    30.04
 unit1   2021-09-01 07:48:24.00    31.825
 unit1   2021-09-01 07:49:06.00    30.26
 unit1   2021-09-01 07:49:24.00    33.875
 unit1   2021-09-01 07:49:33.00    33.54
 unit1   2021-09-01 07:50:21.00    37.235
 unit1   2021-09-01 07:50:43.00    15.22
 unit1   2021-09-01 07:50:51.00    13.435
 unit1   2021-09-01 07:51:24.00    17.03
 unit1   2021-09-01 07:52:17.00    16.915
 unit1   2021-09-01 07:52:33.00    18.435
 unit1   2021-09-01 07:52:54.00    16.805
 unit1   2021-09-01 07:53:15.00    76.225
 unit1   2021-09-01 07:53:29.00    24.375
 unit1   2021-09-01 07:54:21.00    29.925
 unit1   2021-09-01 07:55:14.00    17.39
 unit1   2021-09-01 07:55:29.00    22.975
 unit1   2021-09-01 07:56:19.00    8.015
 unit1   2021-09-01 07:56:28.00    42.68
 unit1   2021-09-01 07:56:57.00    24.33
 unit1   2021-09-01 07:57:16.00    33.575
 unit1   2021-09-01 07:57:25.00    13.215
 unit1   2021-09-01 07:57:39.00    31.245
 unit1   2021-09-01 07:58:16.00    90.705
 unit1   2021-09-01 07:58:31.00    58.665
 unit1   2021-09-01 07:58:47.00    44.06
 unit1   2021-09-01 07:59:16.00    40.32
 unit2   2021-09-01 07:57:25.00    13.215
 unit2   2021-09-01 07:57:39.00    31.245
 unit2   2021-09-01 07:58:16.00    60.705
 unit2   2021-09-01 07:58:31.00    58.665
 unit2   2021-09-01 07:58:47.00    44.06
 unit2   2021-09-01 07:59:16.00    40.32
 unit3   2021-09-01 07:57:25.00    43.215
 unit3   2021-09-01 07:57:39.00    71.245
 unit3   2021-09-01 07:58:16.00    60.705
 unit3   2021-09-01 07:58:31.00    98.665
 unit3   2021-09-01 07:58:47.00    44.06
 unit3   2021-09-01 07:59:16.00    40.32

This is my table 2 (unit_table):

 UnitNo  userid
 --------------
 unit1   1122
 unit2   1122
 unit3   1122
 unit4   5577
 unit5   5577
 unit6   8888

And this is my table 3 (users_table):

 userid   username
--------------------
 1122     Rolly
 1122     Efren
 1222     Mark
 5577     Bert
 5577     Jay
 8888     Eric

CodePudding user response：

I have tried to simplify ..... will return duplicate figures because you have two names associated to userid 1122.

SELECT
  avh.UnitNo,
  SUM(CASE WHEN speed >= 41 AND speed < 71 THEN 1 ELSE 0 END) AS overord,
  SUM(CASE WHEN speed >= 71 AND speed < 91 THEN 1 ELSE 0 END) AS overex,
  SUM(CASE WHEN speed >= 91 THEN 1 ELSE 0 END) AS overc,
  SUM(CASE WHEN speed >= 41 THEN 1 ELSE 0 END) AS ttover,
  ut.username
FROM unitlog_table AS avh
INNER JOIN unit_table AS av
  ON avh.UnitNo = av.UnitNo
INNER JOIN users_table AS ut
  ON av.userid = ut.userid
WHERE (avh.timestamp >= '2021-09-01 00:00:00.00')
AND (avh.timestamp <= '2021-09-01 08:00:00.00')
GROUP BY avh.UnitNo,
         ut.username,
         ut.userid
HAVING (ut.userid = 1122)

CodePudding user response：

It looks like you really want conditional aggregation. You just need to join some tables, group, then conditionally aggregate them

The condition speed >= 41 and speed >= 71 and speed >= 91 seems logically incorrect.
And the condition between '2021-09-01 00:00:00.00' and '2021-09-01 08:00:00.00' also seems wrong.
Always use half open intervals >= AND <rather thanBETWEEN`

select avh.UnitNo, 
    count(case when speed >= 41 then 1 end) as overord,
    count(case when speed >= 71 then 1 end) as overex ,
    count(case when speed >= 91 then 1 end) as overc,
    count(case when speed >= 41 and speed >= 71 and speed >= 91 then 1 end) as ttover,
    username
from 
    unitlog_table avh 
inner join
    users_table ut on ut.UnitNo = av.UnitNo 
where 
    avh.UnitNo in (select UnitNo from unit_table where userid = '1122')  
    and timestamp between '2021-09-01 00:00:00.00' and '2021-09-01 08:00:00.00'
group by
    avh.UnitNo;