[thelist] ASP.NET: UNION query not returning expected results

Joel D Canfield joel at streamliine.com
Thu Sep 27 16:17:48 CDT 2007


"WHERE E.OfficeID IS NULL" - yeah, that's the ticket

I modified it to include Employees.id as EmpID in the first select,
EmpID2 in the second, so .NET can apply the FilterExpression "EmpID =
{0}" to only the first, ostensibly

The query below returns the expected results in the query analyzer, but
still, in the web page (with "WHERE E.id = 57" removed 'cause that's
what the FilterExpression is spozed to do) it's either not doing the
UNION or it's applying the EmpID filter to both parts.

I suspect it's not a SQL issue, although I'm much happy to have the
shiny new query.

SELECT E.id               AS EmpID
     , O.id               AS OfficeID
     , O.number           AS Office 
  FROM dbo.Employees as E
INNER
  JOIN dbo.Offices as O
    ON O.id = E.OfficeID
 WHERE E.id = 57 
UNION ALL
SELECT E.id               AS EmpID2
     , O.id  
     , O.number  
  FROM dbo.Offices as O
LEFT OUTER
  JOIN dbo.Employees as E
    ON E.OfficeID = O.id
 WHERE E.OfficeID IS NULL
ORDER BY Office



More information about the thelist mailing list