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

On Thu, 25 Oct 2007 07:30:01 -0700, Matt M wrote:

(snip)
Does this help? Your sample query you showed me looks promising but it may
take me a little bit to wrap my brain around it.

Hi Matt,

The main reason that this query is ntroublesome to come up with, is that
the design is bad. I'm not sure if you are able to change that (never
worked with GP, and after seeing their table and column names, I hope to
keep it that way). An order should be in the same table, whether it's
partially or fully shipped, or not shipped at all.

Anyway, if you are stuck with the design, than try sometinh like this:

SELECT base.SOPTYPE, base.SOPNUMBE, base.ORIGNUMB,
COALESCE(orig1.SOPTYPE, orig2.SOPTYPE),
COALESCE(orig1.SOPNUMBE, orig2.SOPNUMBE)
FROM table2 AS base
LEFT JOIN table1 AS orig1
ON orig1.SOPNUMBE = base.ORIGNUMBE
LEFT JOIN table2 AS orig2
ON orig2.SOPNUMBE = base.ORIGNUMBE
WHERE base.SOPTYPE = 3; -- Invoices only

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

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

Relevant Pages

  • Re: How to handle concurrency issue with better performance?
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... Then for timestamp, it will be unique? ... Collisions occur because your design permits ... select command). ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ORDER BY in VIEW not working
    ... As an object-oriented programmer, I expect to be able to encapsulate ... The relevant design principle is that of Orthogonal Design. ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)
  • Re: Target number of database reads per user
    ... Yeah, just to echo what Andy is saying, if you want to have the question ... If you can post code/database design we could all take a look. ... Pro SQL Server 2000 Database Design - ...
    (microsoft.public.sqlserver.programming)
  • Re: Access Form connected to SQL table in Access Data Project (.adp file)
    ... Under the previous design, he would just open the form and could ... navigate through the last few orders by clicking "next record" a few times. ... > the form what records it should load. ... we are NOT even using sql server yet. ...
    (microsoft.public.access.conversion)
  • Re: Database design question
    ... Pro SQL Server 2000 Database Design ... Please reply to the newsgroups only unless you are ...
    (microsoft.public.sqlserver.programming)