How do I get rid of a whole row of NULL when I'm using a UNION?

Question

Select * from OPENQUERY (PORTAL, ''SELECT st.last AS "Last Name", st.first AS "First Name", ct.DeviceName, SUM (ct.PageCount) AS "Count", pm.TotalPerPage FROM printer_stats.Counts ct INNER JOIN dv.staff st on st.id = ct.UserID LEFT JOIN network_map.PrintersToDevices ptd ON ptd.DeviceID = ct.DeviceID LEFT JOIN network_map.PrinterModels pm ON ptd.PrinterID = pm.PrinterID WHERE ct.UserID = "'+@UserID+'" and ct.PageCount > 0 and ct.Date >= "'+@AcademicYTD+'" UNION SELECT stu.last_name AS "Last Namae", stu.first_name AS "First Name", ct.DeviceName,SUM(ct.PageCount) AS "Count", pm.TotalPerPage FROM printer_stats.Counts ct INNER JOIN dv.student stu on stu.username = ct.Username LEFT JOIN network_map.PrintersToDevices ptd ON ptd.DeviceID = ct.DeviceID LEFT JOIN network_map.PrinterModels pm ON ptd.PrinterID = pm.PrinterID WHERE ct.UserID = "'+@UserID+'" and ct.PageCount > 0 and ct.Date >= "'+@AcademicYTD+'" GROUP BY ct.DeviceName ORDER BY 4 DESC'') I get results like this: Last Name First Name DeviceName Count TotalPerPage Stewart Jesse BG-LMC-HP4100-01 39 0.0287581 Stewart Jesse BG-LMC-HP4100-02 9 0.0287581 Stewart Jesse BG-903-RI4210 2 0.013341 Stewart Jesse BG-218-HP4000 1 0.013341 NULL NULL NULL NULL NULL I don't want the last row of all NULLS. I know they are coming from the first SELECT for Staff since there should be no matching rows: The user is a Student, so there wouldn't be a match. I need to know how to only show rows with data in all fields. JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

If you know what part of your UNION is providing the Null-row, you should first look at it, ensure that it returns what you want and fix your problem there. However, if you're otherwise satisfied that the query is as it should be, you can always do a hack with an EXCEPT SELECT NULL-construction: (I've included surrounding lines for reference) ... WHERE ct.UserID = "'+@UserID+'" and ct.PageCount > 0 and ct.Date >= "'+@AcademicYTD+'" EXCEPT SELECT NULL AS "Last Name", NULL AS "First Name", NULL as DeviceName, NULL AS "Count", NULL AS TotalPerPage GROUP BY ct.DeviceName ORDER BY 4 DESC'')