Search code examples
sqltable-alias

Sql multiple nested selects

As you can see, the beginning and the end of the week are selected with this code. I'm new to sql and I understand what's going on here in general, but I can't explain it in detail. For example ;

(select lookupweekid-1 from lookupday where dateoftransaction = date) t2 (lookupweekid)

This one confused me a lot. (...) t2 (...), <-I don't understand what the parenthesis in the last part is telling

select start_of_week,end_of_week from
(select dateoftransaction
 from lookupday t1,
      (select lookupweekid-1 from lookupday 
       where dateoftransaction = date) t2 (lookupweekid)
 where t1.lookupweekid=t2.lookupweekid
   and t1.weekdaynumber=1) t3 (start_of_week)
,(select dateoftransaction
  from lookupday t1,
       (select lookupweekid-1 from lookupday 
        where dateoftransaction = date) t2 (lookupweekid)
  where t1.lookupweekid=t2.lookupweekid
  and t1.weekdaynumber=7) t4 (end_of_week)
;
Solution

  • t4 (end_of_week) is a table alias that also defines a column alias. This avoids having to specify the column aliases inside the derived table.

    So this:

    (select dateoftransaction
 from lookupday t1 ... 
 where t1.lookupweekid=t2.lookupweekid
 and t1.weekdaynumber=7) t4 (end_of_week)

    is equivalent to:

    (select dateoftransaction as end_of_week
 from lookupday t1 ... 
 where t1.lookupweekid=t2.lookupweekid
 and t1.weekdaynumber=7) t4

    This is e.g. explained in the Postgres manual