Re: Pulling data that could be in one of 2 tables

On Fri, 26 Oct 2007 09:40:00 -0700, Matt M wrote:

(snip)
Probably not the most efficient way to do it but it got the job done and my
numbers came out exact.

Hi Matt,

This way is probably more efficient:

SELECT Main.SOPTYPE, Main.SOPNUMBE, Main.ORIGNUMB,
Main.DOCID, Main.DOCDATE, Main.INVODATE,
Main.MSTRNUMB, Main.DOCAMNT,
COALESCE(Original.DOCID, History.DOCID) AS ORIGDOCID
FROM SOP30200 AS Main
LEFT JOIN SOP10100 AS Original
ON Original.SOPNUMBE = Main.ORIGNUMB
LEFT JOIN SOP30200 AS History
ON History.SOPNUMBE = Main.ORIGNUMB
AND AND History.SOPTYPE = '2'
WHERE Main.SOPTYPE = '3'
AND Main.DOCDATE >= @BeginDate
AND Main.DOCDATE <= @EndDate;

(untested)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
.

Relevant Pages

  • Re: Query Analyzer Erorr: Resource is low. Some results are dropped
    ... What exact build are you running? ... font or display resolution have any effect)? ... >When I use Query Analyzer on my server to either query the local SQL Server or a remote SQL Server, ...
    (microsoft.public.sqlserver.server)
  • Re: Migrating away from MS-Access
    ... that, in fact, the amount of information being sent *is* smaller. ... convert the back end to SQL Server and fix the Access ... I do think that it is definitly the case the front end needs a 70-80% rewrite to work with sqlserver There must be reasons why people develop in VB.net. ...
    (comp.databases.ms-access)
  • RE: Authentication Error with HTTP EndPoints
    ... Could you please post the exact steps you performed to create the HTTP ... EndPoints and the URL you used to connect to the SQL Server? ...
    (microsoft.public.sqlserver.xml)
  • RE: BCP.EXE fails in 90 where it succeeded in 80
    ... I'll be careful to do so in the future, ... v8.0 (exact same SQL 2005 server, exact same BCP command line)? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.tools)
  • Re: New to DB Development
    ... Thanks Jeff. ... "hit" the exact page at the exact time, two or more users "on" the exact ... Concurrent users means web ... plus a direct migration to SQL Server. ...
    (microsoft.public.inetserver.asp.db)