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

[Tab Alleman]

Yep, makes perfect sense to me that the Filter will be applied to both sides of your UNION.  I'd consider making a sproc to be executed rather than adhoc SQL.  

> -----Original Message-----
> From: thelist-bounces at lists.evolt.org
> [mailto:thelist-bounces at lists.evolt.org]On Behalf Of Joel D Canfield
> Sent: Thursday, September 27, 2007 5:18 PM
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] ASP.NET: UNION query not returning expected
> results
> 
> 
> "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
> -- 
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester 
> and archives of thelist go to: http://lists.evolt.org 
> Workers of the Web, evolt ! 
>