Posted by Ed Murphy on 08/09/06 18:27

On 9 Aug 2006 05:00:20 -0700, "jason.langdale@gmail.com"
<jason.langdale@gmail.com> wrote:

>all three tables contain the same
>SOPNUMBE column but the rows in each table are unique.

But the SOPNUMBE values are the same, so you should only include
one. (If they're not the same, then you can't join the tables
that way.)

>Since on of these
>tables does not have all the fields I want to select, I can't do a
>simple union; which would be ideal for me.

Try the following:

CREATE VIEW [historic_sales_2] as
SELECT
SOP30200.SOPNUMBE,
SOP30200.DOCDATE,
SOP30200.MSTRNUMB,
SOP30200.CUSTNMBR,
SOP30200.CUSTNAME,
SOP30200.DOCAMNT,
SOP30200.LOCNCODE,
SOP30200.VOIDSTTS,
SOP30200.SOPTYPE,
SOP10200.ITEMNMBR,
SOP10200.QUANTITY,
SOP10200.UNITPRCE,
SOP10200.XTNDPRCE,
SOP10200.SLPRSNID,
SOP10200.SALSTERR
FROM cvi.dbo.SOP30200,cvi.dbo.SOP10200
WHERE SOP30200.SOPNUMBE = SOP10200.SOPNUMBE
UNION ALL
SELECT
SOP10100.SOPNUMBE,
SOP10100.DOCDATE,
SOP10100.MSTRNUMB,
SOP10100.CUSTNMBR,
SOP10100.CUSTNAME,
SOP10100.DOCAMNT,
SOP10100.LOCNCODE,
SOP10100.VOIDSTTS,
SOP10100.SOPTYPE,
NULL AS ITEMNMBR,
NULL AS QUANTITY,
NULL AS UNITPRCE,
NULL AS XTNDPRCE,
NULL AS SLPRSNID,
NULL AS SALSTERR
FROM cvi.dbo.SOP10100,cvi.dbo.SOP10200
WHERE SOP10100.SOPNUMBE = SOP10200.SOPNUMBE

• Next in forum: format files for use with bulk insert
• Prev in forum: Re: Using Union and Join in a single view
• Thread view: Re: Using Union and Join in a single view

[Reply to this message]