[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