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

[Joel D Canfield]

For my 'employee edit' tool, I need my offices dropdownlist to include
the employee's current office, plus all empty offices (we're assuming
that if an office id exists in the offices table, but is not assigned as
an officeid in the employees table, it's empty.)

This query returns the expected results in the query analyzer:

     SELECT 
       dbo.Employees.id AS EmpID
     , dbo.Employees.officeid AS OfficeID
     , dbo.Offices.number AS Office 
     FROM Employees 
LEFT 
     OUTER JOIN 
     Offices 
     ON
     dbo.Employees.officeid = dbo.Offices.id 
     WHERE dbo.Employees.id = 57 
UNION 
     SELECT 
       dbo.Employees.id AS EmpID2
     , dbo.Offices.id AS OfficeID
     , dbo.Offices.number AS Office 
     FROM Offices 
LEFT 
     OUTER JOIN 
     Employees 
     ON 
     dbo.Offices.id = dbo.Employees.officeid 
     WHERE dbo.Offices.id NOT IN 
     (
       SELECT DISTINCT 
       dbo.Employees.officeid 
       FROM dbo.Employees 
       WHERE dbo.Employees.officeid IS NOT NULL
     )
     ORDER BY dbo.Offices.number

This returns only the current employee and office to the DDL in the web
page:

    <asp:SqlDataSource 
        ID="srcOffices" 
        runat="server" 
        ConnectionString="<%$ ConnectionStrings:cnxHelm %>"
        FilterExpression="EmpID = {0}" 
        SelectCommand="SELECT dbo.Employees.id AS EmpID,
dbo.Employees.officeid AS OfficeID, dbo.Offices.number AS Office FROM
Employees LEFT OUTER JOIN Offices ON dbo.Employees.officeid =
dbo.Offices.id UNION SELECT dbo.Employees.id AS EmpID2, dbo.Offices.id
AS OfficeID, dbo.Offices.number AS Office from Offices LEFT OUTER JOIN
Employees ON dbo.Offices.id = dbo.Employees.officeid where
dbo.Offices.id NOT IN (SELECT DISTINCT dbo.Employees.officeid FROM
dbo.Employees WHERE dbo.Employees.officeid IS NOT NULL) ORDER BY
dbo.Offices.number"
    >
        <FilterParameters>
            <asp:QueryStringParameter Name="EmpID" QueryStringField="id"
/>
        </FilterParameters>
    </asp:SqlDataSource>    

        <asp:DropDownList 
            ID="OfficeDDL" runat="server" 
            DataSourceID="srcOffices"
            DataTextField="Office" 
            DataValueField="OfficeID"
            SelectedValue='<%# Bind("OfficeID") %>' 
        >
        </asp:DropDownList>

I wish .NET wouldn't try to be so helpful with the SQL.

Thoughts?

joel