Home How do I insert left joins in a nested query?
Reply: 1

How do I insert left joins in a nested query?

serenity
1#
serenity Published in 2018-02-13 15:44:27Z

First of all I would like to thank the friends who helped this complex and difficult query.

I have three tables

Table 1

 StaffId     FirstName       LastName   staffType
---------------------------------------
   1          Adam            Sorme      Student 
   2          Lara            Sandra     Teacher
   3          Jack            Jones      Student

Table 2

 GateId   GateName  
 ---------------------------------------
   1        frontDoor
   2        superDoor

Table 3

Id transitionDate     GateId  StaffId 
 ---------------------------------------
1  2018-01-1 08:00:00    1     1
2  2018-01-1 10:00:00    2     1
3  2018-01-1 20:00:00    2     1
4  2018-01-2 07:00:00    1     2
5  2018-01-2 10:00:00    1     3
6  2018-01-9 12:00:00    2     2

I want the first and last movements of students for each day. Value must be set to null if no movement is available between the specified dates

transitionDate> '2018-01-1 00:00:00 000' 
 and transitionDate< '2018-01-03 00:00:00 000'

OUTPUT:

  Id     Date    MinTransitionDate    MaxTransitionDate    FirstGateName LastGateName    StaffId    StaffType
  1   2018-01-01  2018-01-1 08:00:00 2018-01-1 20:00:00    frontDoor      superDoor         1         Student
  2   2018-01-01  null                null                  null           null             3         student
  3   2018-01-02  null                null                  null           null             1         student
  4   2018-01-02  2018-01-2 10:00:00  null                 frontDoor       null             3         student

The following query is partially working.

select s.staffId, d.dte,
       min(t.transitionDate) as first_change,
       max(t.transitionDate) as first_change,
       max(case when seqnum_asc = 1 then gateId end) as first_gateid,
       max(case when seqnum_desc = 1 then gateId end) as last_gateid
from (select s.* from Staff s where stafftype = 'Student') s cross join
     (select distinct cast(transitionDate as date) as dte from Transitions) d left join
     (select t.*,
             row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate) as seqnum_asc,
             row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate desc) as seqnum_desc
      from Transitions t
     ) t
     on cast(t.transitiondate as date) = d.dte and
        t.staffId = s.staffId and
        1 in (t.seqnum_asc, t.seqnum_desc)
group by s.staffId, d.dte;

Here is a SQL Fiddle.

How do I add firstGateName and LastGateName to this query result?

Kirk Broadhurst
2#
Kirk Broadhurst Reply to 2018-02-13 20:12:36Z

You can just join your existing query to the Gates table to get those names, i.e.

<existing query>
inner join Gates g1 on g1.gateId = (required gate id)

In your case you can join using the aggregate value you've

select 
    q.*, 
    g1.GateName as first_gate_name,
    g2.GateName as last_gate_name
from 
-- use existing query as a subquery, so we can easily use the first/last_gateid values
(
    select s.staffId, d.dte,
           min(t.transitionDate) as first_change,
           max(t.transitionDate) as last_change,
           max(case when seqnum_asc = 1 then gateId end) as first_gateid,
           max(case when seqnum_desc = 1 then gateId end) as last_gateid   
    from (select s.* from Staff s where stafftype = 'Student') s cross join
         (select distinct cast(transitionDate as date) as dte from Transitions) d left join
         (select t.*,
                 row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate) as seqnum_asc,
                 row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate desc) as seqnum_desc
          from Transitions t
         ) t
         on cast(t.transitiondate as date) = d.dte and
            t.staffId = s.staffId and
            1 in (t.seqnum_asc, t.seqnum_desc)
    group by s.staffId, d.dte
) q
-- join on the appropriate gate ids      
inner join Gates g1 on g1.gateId = q.first_gateid
inner join Gates g2 on g2.gateId = q.last_gateid
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.356626 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO