[thelist] ASP.NET: UNION query not returning expected results
Tab Alleman
talleman at Lumpsum.com
Thu Sep 27 16:24:59 CDT 2007
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 !
>
More information about the thelist
mailing list