Select statement -SQL 05

[emmim44]

Hi all, I am getting this error: ...Any suggestions...
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
--------------------
SELECT     TOP (100) PERCENT EID, MAX(loginDate) AS LoginDate, DeptUserID
FROM         (SELECT     dbo.User_TBL.Username AS EID, dbo.User_TBL.LastLogIn AS loginDate, dbo.DeptUser.DeptUserID
                       FROM          dbo.User_TBL INNER JOIN
                                              dbo.DeptUser ON dbo.User_TBL.User_ID = dbo.DeptUser.User_ID
                       UNION
                       SELECT     Username AS EID, LastLogIn AS LoginDate, DeptID
                       FROM         dbo.User_TBL AS AdminUser_1
                       UNION
SELECT     TOP (100) PERCENT (dbo.CCFUser.First_Name + ' ' + dbo.CCFUser.Last_Name + '('+ dbo.CCFUser.Work_Status + ')') AS FullName, dbo.CCFUser.EID, dbo.CCFUser.CCFRole,dbo.CCFUser.CSGID, dbo.Department.DeptID, dbo.CCFUser.loginDate,dbo.CCFUser.ModifyDate
                       FROM         dbo.CCFUser INNER JOIN
                                             dbo.Sheet ON dbo.CCFUser.EID = dbo.Sheet.EID INNER JOIN
                                             dbo.SnapShot ON dbo.Sheet.SnapshotID = dbo.SnapShot.SnapshotID INNER JOIN
                                             dbo.Department ON dbo.SnapShot.pDeptID = dbo.Department.DeptID
where
dbo.CCFUser.Deleted = 0) AS A
GROUP BY EID, DeptUserID
ORDER BY EID, DeptUserID

[ilssac · Accepted solution]

emmim44 wrote:

SELECT

      TOP (100) PERCENT EID, MAX(loginDate) AS LoginDate, DeptUserID
SELECT

      TOP (100) PERCENT (dbo.CCFUser.First_Name + ' ' + dbo.CCFUser.Last_Name + '('+ dbo.CCFUser.Work_Status + ')') AS FullName,      dbo.CCFUser.EID,

     dbo.CCFUser.CCFRole,

     dbo.CCFUser.CSGID,

     dbo.Department.DeptID,

     dbo.CCFUser.loginDate,

     dbo.CCFUser.ModifyDate

Just as the error says.  The Select clauses of all parts of an Union query must have the same number of columns.  Not two fields in the first Select and seven fields in the second Select like you have here.

If you don't want to select any data in the first part you can use nulls as place holders.

SELECT
      TOP (100) PERCENT EID, MAX(loginDate) AS LoginDate,
      DeptUserID,
     NULL AS CCFRole,
     NULL AS ....,
     NULL AS ....,
     NULL AS ...,
     NULL AS ...
SELECT
      TOP (100) PERCENT (dbo.CCFUser.First_Name + ' ' + dbo.CCFUser.Last_Name + '('+ dbo.CCFUser.Work_Status + ')') AS FullName,      dbo.CCFUser.EID,
     dbo.CCFUser.CCFRole,
     dbo.CCFUser.CSGID,
     dbo.Department.DeptID,
     dbo.CCFUser.loginDate,
     dbo.CCFUser.ModifyDate

[emmim44]

But the fist section doesnt have those fields... i need to select those fields... how would I do that?

[ilssac]

See my update I was posting at the same time you where replying.

P.S. It is important that the fields are also in the same order on both Select clauses.

[emmim44]

how about the group section? Will get an error if i dont use those fields here?

GROUP BY EID, DeptUserID
ORDER BY EID, DeptUserID

[ilssac]

No, you can group by or order by any combonation of fields you choose.

[Dan_Bracuk]

You can either run two queries or select constants.  Neither will merge common records which is often the objective of a union query.

[emmim44]

Thank you guysss