CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Thilina Sandunsiri
Thilina Sandunsiri

Reputation: 580

Join two SQL select statements and get both results in to single

I want to join these two SQL select statements and get both results as a single result.

--1st select statement
SELECT TA.DevID, TA.Task, TA.AssignDate,  TA.DevStart,TA.TaskType, TA.EstimateTime, TA.Status, TA.DevFinish, TAKPI.actAsignTime,  TAKPI.actTime, TAKPI.KPI
FROM TT_TaskAssign TA
INNER JOIN HR_EmployeeMaster1 EM
on TA.DevID=EM.EmpNo 
INNER JOIN dbo.TT_TaskAsignKPI TAKPI
ON TAKPI.AssignID = TA.AssignID 
WHERE TA.DevID='1435'

--2nd select statement
SELECT  TT_TaskAssign.DevID AS DevID  ,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName as Developer, SUM( CASE WHEN TT_TaskAssign.Status='Done' THEN 1 ELSE 0 END ) as DoneProjects , SUM( CASE WHEN TT_TaskAssign.Status='Developing' THEN 1 ELSE 0 END ) as DevelopingPrjects, SUM( CASE WHEN TT_TaskAssign.Status='Assign' THEN 1 ELSE 0 END ) as AssignedPending, SUM(CONVERT(int, TAKPI.KPINum)) AS KPINum 
FROM TT_TaskAssign inner join Project_Master on TT_TaskAssign.ProID=Project_Master.Project_Code Inner Join HR_EmployeeMaster1 on TT_TaskAssign.DevID=HR_EmployeeMaster1.EmpNo INNER JOIN dbo.TT_TaskAsignKPI TAKPI ON TAKPI.AssignID = TAKPI.AssignID
WHERE TT_TaskAssign.DevID='1435'
GROUP BY  TT_TaskAssign.DevID,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName
ORDER BY SUM(CONVERT(int, TAKPI.KPINum)) DESC

I did it in follwing way

SELECT T1.*, T2.*
FROM 
  (SELECT TA.DevID, TA.Task, TA.AssignDate,  TA.DevStart,TA.TaskType, TA.EstimateTime, TA.Status, TA.DevFinish, TAKPI.actAsignTime,  TAKPI.actTime, TAKPI.KPI
  FROM TT_TaskAssign TA
  INNER JOIN HR_EmployeeMaster1 EM
  on TA.DevID=EM.EmpNo 
  INNER JOIN dbo.TT_TaskAsignKPI TAKPI
  ON TAKPI.AssignID = TA.AssignID 
  WHERE TA.DevID='1435') T1

INNER JOIN
  (SELECT  TT_TaskAssign.DevID AS DevID  ,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName as Developer, SUM( CASE WHEN TT_TaskAssign.Status='Done' THEN 1 ELSE 0 END ) as DoneProjects , SUM( CASE WHEN TT_TaskAssign.Status='Developing' THEN 1 ELSE 0 END ) as DevelopingPrjects, SUM( CASE WHEN TT_TaskAssign.Status='Assign' THEN 1 ELSE 0 END ) as AssignedPending, SUM(CONVERT(int, TAKPI.KPINum)) AS KPINum 
   FROM TT_TaskAssign inner join Project_Master on TT_TaskAssign.ProID=Project_Master.Project_Code Inner Join HR_EmployeeMaster1 on TT_TaskAssign.DevID=HR_EmployeeMaster1.EmpNo INNER JOIN dbo.TT_TaskAsignKPI TAKPI ON TAKPI.AssignID = TAKPI.AssignID
  WHERE TT_TaskAssign.DevID='1435'
  GROUP BY  TT_TaskAssign.DevID,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName
  ORDER BY SUM(CONVERT(int, TAKPI.KPINum)) DESC) T2

ON T1.DevID=T2.DevID

Now It's giving following error 

    Msg 1033, Level 15, State 1, Line 17 
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Please somebody help me to fix this error

Upvotes: 0

Views: 58

Answers (6)

Geewers
Geewers

Reputation: 206

You could join also by using a CTE and move the ORDER BY out to the main final SELECT.

;WITH T1
AS
(
    SELECT TA.DevID, TA.Task, TA.AssignDate,  TA.DevStart,TA.TaskType, TA.EstimateTime, TA.Status, TA.DevFinish, TAKPI.actAsignTime,  TAKPI.actTime, TAKPI.KPI
        FROM TT_TaskAssign TA
        INNER JOIN HR_EmployeeMaster1 EM on TA.DevID=EM.EmpNo 
        INNER JOIN dbo.TT_TaskAsignKPI TAKPI  ON TAKPI.AssignID = TA.AssignID 
    WHERE TA.DevID='1435'
),
T2 
AS
(
    SELECT  TT_TaskAssign.DevID AS DevID  ,
        HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName as Developer, 
        SUM( CASE WHEN TT_TaskAssign.Status='Done' THEN 1 ELSE 0 END ) as DoneProjects , 
        SUM( CASE WHEN TT_TaskAssign.Status='Developing' THEN 1 ELSE 0 END ) as DevelopingPrjects, 
        SUM( CASE WHEN TT_TaskAssign.Status='Assign' THEN 1 ELSE 0 END ) as AssignedPending, 
        SUM(CONVERT(int, TAKPI.KPINum)) AS KPINum 
    FROM TT_TaskAssign 
        INNER JOIN Project_Master on TT_TaskAssign.ProID=Project_Master.Project_Code 
        INNER JOIN HR_EmployeeMaster1 on TT_TaskAssign.DevID=HR_EmployeeMaster1.EmpNo 
        INNER JOIN dbo.TT_TaskAsignKPI TAKPI ON TAKPI.AssignID = TAKPI.AssignID
    WHERE TT_TaskAssign.DevID='1435'
    GROUP BY  TT_TaskAssign.DevID,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName   
)
SELECT T1.*, T2.*
FROM T1 
    JOIN T2 ON T1.DevID=T2.DevID
ORDER BY T2.KPINum DESC

Upvotes: 0

sqluser
sqluser

Reputation: 5672

SELECT T1.*, T2.*
FROM 
  (SELECT TA.DevID, TA.Task, TA.AssignDate,  TA.DevStart,TA.TaskType, TA.EstimateTime, TA.Status, TA.DevFinish, TAKPI.actAsignTime,  TAKPI.actTime, TAKPI.KPI
  FROM TT_TaskAssign TA
  INNER JOIN HR_EmployeeMaster1 EM
  on TA.DevID=EM.EmpNo 
  INNER JOIN dbo.TT_TaskAsignKPI TAKPI
  ON TAKPI.AssignID = TA.AssignID 
  WHERE TA.DevID='1435') T1

INNER JOIN
  (SELECT  TT_TaskAssign.DevID AS DevID  ,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName as Developer, SUM( CASE WHEN TT_TaskAssign.Status='Done' THEN 1 ELSE 0 END ) as DoneProjects , SUM( CASE WHEN TT_TaskAssign.Status='Developing' THEN 1 ELSE 0 END ) as DevelopingPrjects, SUM( CASE WHEN TT_TaskAssign.Status='Assign' THEN 1 ELSE 0 END ) as AssignedPending, SUM(CONVERT(int, TAKPI.KPINum)) AS KPINum 
   FROM TT_TaskAssign inner join Project_Master on TT_TaskAssign.ProID=Project_Master.Project_Code Inner Join HR_EmployeeMaster1 on TT_TaskAssign.DevID=HR_EmployeeMaster1.EmpNo INNER JOIN dbo.TT_TaskAsignKPI TAKPI ON TAKPI.AssignID = TAKPI.AssignID
  WHERE TT_TaskAssign.DevID='1435'
  GROUP BY  TT_TaskAssign.DevID,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName) T2

ON T1.DevID=T2.DevID
ORDER BY SUM(CONVERT(int, T2.KPINum)) DESC

Upvotes: 0

Ambareesh Surendran
Ambareesh Surendran

Reputation: 508

Use (#) temporary tables for storing intermediate results..

try this...

        SELECT TA.DevID, TA.Task, TA.AssignDate,  TA.DevStart,TA.TaskType, TA.EstimateTime, TA.Status, TA.DevFinish, TAKPI.actAsignTime,  TAKPI.actTime, TAKPI.KPI
        INTO    #t1
        FROM TT_TaskAssign TA
        INNER JOIN HR_EmployeeMaster1 EM
        on TA.DevID=EM.EmpNo 
        INNER JOIN dbo.TT_TaskAsignKPI TAKPI
        ON TAKPI.AssignID = TA.AssignID 
        WHERE TA.DevID='1435'


        SELECT  TT_TaskAssign.DevID AS DevID  ,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName as Developer, SUM( CASE WHEN TT_TaskAssign.Status='Done' THEN 1 ELSE 0 END ) as DoneProjects , SUM( CASE WHEN TT_TaskAssign.Status='Developing' THEN 1 ELSE 0 END ) as DevelopingPrjects, SUM( CASE WHEN TT_TaskAssign.Status='Assign' THEN 1 ELSE 0 END ) as AssignedPending, SUM(CONVERT(int, TAKPI.KPINum)) AS KPINum 
        INTO        #t2
        FROM TT_TaskAssign inner join Project_Master on TT_TaskAssign.ProID=Project_Master.Project_Code Inner Join HR_EmployeeMaster1 on TT_TaskAssign.DevID=HR_EmployeeMaster1.EmpNo INNER JOIN dbo.TT_TaskAsignKPI TAKPI ON TAKPI.AssignID = TAKPI.AssignID
        WHERE TT_TaskAssign.DevID='1435'
        GROUP BY  TT_TaskAssign.DevID,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName
        ORDER BY SUM(CONVERT(int, TAKPI.KPINum)) DESC

        SELECT  T1.*, T2.*
        FROM        #t1 T1
        INNER JOIN  #t2 T2
            ON T1.DevID=T2.DevID

Upvotes: 1

kvorobiev
kvorobiev

Reputation: 5070

You could move order by out of inner query

SELECT T1.*, T2.*
FROM 
  (SELECT TA.DevID, TA.Task, TA.AssignDate,  TA.DevStart,TA.TaskType, TA.EstimateTime, TA.Status, TA.DevFinish, TAKPI.actAsignTime,  TAKPI.actTime, TAKPI.KPI
  FROM TT_TaskAssign TA
  INNER JOIN HR_EmployeeMaster1 EM
  on TA.DevID=EM.EmpNo 
  INNER JOIN dbo.TT_TaskAsignKPI TAKPI
  ON TAKPI.AssignID = TA.AssignID 
  WHERE TA.DevID='1435') T1
INNER JOIN
  (SELECT  TT_TaskAssign.DevID AS DevID  ,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName as Developer, SUM( CASE WHEN TT_TaskAssign.Status='Done' THEN 1 ELSE 0 END ) as DoneProjects , SUM( CASE WHEN TT_TaskAssign.Status='Developing' THEN 1 ELSE 0 END ) as DevelopingPrjects, SUM( CASE WHEN TT_TaskAssign.Status='Assign' THEN 1 ELSE 0 END ) as AssignedPending, SUM(CONVERT(int, TAKPI.KPINum)) AS KPINum 
   FROM TT_TaskAssign inner join Project_Master on TT_TaskAssign.ProID=Project_Master.Project_Code Inner Join HR_EmployeeMaster1 on TT_TaskAssign.DevID=HR_EmployeeMaster1.EmpNo INNER JOIN dbo.TT_TaskAsignKPI TAKPI ON TAKPI.AssignID = TAKPI.AssignID
  WHERE TT_TaskAssign.DevID='1435'
  GROUP BY  TT_TaskAssign.DevID,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName
  ) T2
ON T1.DevID=T2.DevID
ORDER BY T2.KPINum DESC

Upvotes: 0

Brave Soul
Brave Soul

Reputation: 3620

remove order by from second query

SELECT T1.*, T2.*
FROM 
  (SELECT TA.DevID, TA.Task, TA.AssignDate,  TA.DevStart,TA.TaskType, TA.EstimateTime, TA.Status, TA.DevFinish, TAKPI.actAsignTime,  TAKPI.actTime, TAKPI.KPI
  FROM TT_TaskAssign TA
  INNER JOIN HR_EmployeeMaster1 EM
  on TA.DevID=EM.EmpNo 
  INNER JOIN dbo.TT_TaskAsignKPI TAKPI
  ON TAKPI.AssignID = TA.AssignID 
  WHERE TA.DevID='1435') T1

INNER JOIN
  (SELECT  TT_TaskAssign.DevID AS DevID  ,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName as Developer, SUM( CASE WHEN TT_TaskAssign.Status='Done' THEN 1 ELSE 0 END ) as DoneProjects , SUM( CASE WHEN TT_TaskAssign.Status='Developing' THEN 1 ELSE 0 END ) as DevelopingPrjects, SUM( CASE WHEN TT_TaskAssign.Status='Assign' THEN 1 ELSE 0 END ) as AssignedPending, SUM(CONVERT(int, TAKPI.KPINum)) AS KPINum 
   FROM TT_TaskAssign inner join Project_Master on TT_TaskAssign.ProID=Project_Master.Project_Code Inner Join HR_EmployeeMaster1 on TT_TaskAssign.DevID=HR_EmployeeMaster1.EmpNo INNER JOIN dbo.TT_TaskAsignKPI TAKPI ON TAKPI.AssignID = TAKPI.AssignID
  WHERE TT_TaskAssign.DevID='1435'
  GROUP BY  TT_TaskAssign.DevID,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName
  ) T2

ON T1.DevID=T2.DevID

Upvotes: 1

Jens
Jens

Reputation: 69440

As the error message sayed. ORDER BY is not allowed in subquery. If you need the order by clause you have to use it for the whole result of the join.

Upvotes: 1

Related Questions