Union with co-related subquery

• binumon.vijayan

  SSC Enthusiast

  Points: 185

  More actions

  January 24, 2019 at 12:35 am

  #379120

  I am trying to take MAX(date) from the result set of three different tables using a UNION operator. so here is the query I used. But I am getting the error like "The column prefix 'e' doesnot match with the table name or aliases name used in the query. Either the table is not specified in the from clause or it has correlation name which must be used instead". please advise how can i get the result here by referencing the outer table Employee?.

  SELECT FirstName, SecondName, Pay_Date = ((
  SELECT MAX(Pay_Date)FROM (SELECT MAX(Pay_Date) FROM  Employee e1WHERE e1.emp_id = e.emp_idUNIONSELECT MAX(Pay_Date) FROM  Department d1WHERE d1.emp_id = e.emp_idUNIONSELECT MAX(Pay_Date) FROM  Sales s1WHERE s1.emp_id = e.emp_id) p1)
   FROM Employee e

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 24, 2019 at 1:42 am

  #2019913

  Untested:
  SELECT
   e.FirstName,
   e.SecondName,
   x.Pay_Date
  FROM Employee e
  CROSS APPLY ( -- x
   SELECT Pay_Date = MAX(Pay_Date)
   FROM ( -- d
    SELECT e.Pay_Date
    UNION ALL
    SELECT MAX(Pay_Date) FROM Employee e1 WHERE e1.emp_id = e.emp_id
    UNION ALL
    SELECT MAX(Pay_Date) FROM Department d1 WHERE d1.emp_id = e.emp_id                       
    UNION ALL                       
    SELECT MAX(Pay_Date) FROM Sales s1 WHERE s1.emp_id = e.emp_id
   ) d
  ) x

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• sqlservercentral42

  Newbie

  Points: 5

  More actions

  March 12, 2019 at 3:18 am

  #2024174

  /*
drop table employee
go

  create table employee
(
firstname varchar(100),
secondname varchar(100),
emp_id int,
pay_date datetime
)
go

  drop table department
go

  create table department
(
emp_id int,
pay_date datetime
)
go

  drop table sales
go

  create table sales
(
emp_id int,
pay_date datetime
)
go

  insert into employee select 'ok','fine',1,'2017-01-19'
insert into department select 1,'2018-10-24'
insert into sales select 1,'2015-02-15'
go

  select * from employee
go

  select * from department
go

  select * from sales
go
*/

  SELECT a.emp_id, 
       MaxMax(a.pay_date), 
       c.firstname, 
       c.secondname 
FROM   (SELECT emp_id, 
               pay_date 
        FROM   employee 
        UNION ALL 
        SELECT emp_id, 
               pay_date 
        FROM   department 
        UNION ALL 
        SELECT emp_id, 
               pay_date 
        FROM   sales) a 
       JOIN employee c 
         ON a.emp_id = c.emp_id 
GROUP  BY a.emp_id, 
          c.firstname, 
          c.secondname 

  

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  March 12, 2019 at 5:04 am

  #2024185

  SELECT FirstName, SecondName, T.MaxPayDate Pay_Date
  FROM Employee e
 CROSS APPLY(SELECT TOP(1) Pay_Date
               FROM (SELECT MAX(Pay_Date) Pay_Date
                       FROM Employee e1    
                      WHERE e1.emp_id = e.emp_id        
                      UNION ALL       
                     SELECT MAX(Pay_Date) Pay_Date
                       FROM Department d1    
                      WHERE d1.emp_id = e.emp_id        
                      UNION ALL
                     SELECT MAX(Pay_Date) Pay_Date
                       FROM Sales s1    
                      WHERE s1.emp_id = e.emp_id) p1
                      ORDER BY p1.Pay_Date DESC) T(MaxPayDate)

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  March 12, 2019 at 1:18 pm

  #2024264

  Jonathan AC Roberts - Tuesday, March 12, 2019 5:04 AM

  SELECT FirstName, SecondName, T.MaxPayDate Pay_Date
  FROM Employee e
 CROSS APPLY(SELECT TOP(1) Pay_Date
               FROM (SELECT MAX(Pay_Date) Pay_Date
                       FROM Employee e1    
                      WHERE e1.emp_id = e.emp_id        
                      UNION ALL       
                     SELECT MAX(Pay_Date) Pay_Date
                       FROM Department d1    
                      WHERE d1.emp_id = e.emp_id        
                      UNION ALL
                     SELECT MAX(Pay_Date) Pay_Date
                       FROM Sales s1    
                      WHERE s1.emp_id = e.emp_id) p1
                      ORDER BY p1.Pay_Date DESC) T(MaxPayDate)

  Assuming that the emp_id is a unique key (probably the primary key) there is no reason to read the employee table twice.

  SELECT FirstName, SecondName, T.Maxpay_date Pay_Date
  FROM #Employee e
 CROSS APPLY(SELECT TOP(1) Pay_Date
               FROM (SELECT e.Pay_Date
               /*  Removed second scan of employee table and just used the field from the main query. */
                      UNION ALL        
                     SELECT MAX(Pay_Date) Pay_Date
                       FROM  #Department d1   
                      WHERE d1.emp_id = e.emp_id       
                      UNION ALL
                     SELECT MAX(Pay_Date) Pay_Date
                       FROM #Sales s1   
                      WHERE s1.emp_id = e.emp_id) p1
                      ORDER BY p1.Pay_Date DESC) T(Maxpay_date)


  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA