Using Union and Join in a single view

[jason.langdale@gmail.com]

I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5
and table B has field 1,2,3,4,5. I want to do a union on these. (I have
done so successfully if I stop here) I also want to join table C which
has field 1,6,7,8,9. I would like to join on field 1 and bring in the
other fields. I can join table C to A or B. I can union table A and B
but I do not know how to both union A and B then join C. Can someone
please help me? Thanks in advance.

[Robert Klemme]

Re: Using Union and Join in a single view

On 08.08.2006 16:31, jason.langdale@ gmail.com wrote:

I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5
and table B has field 1,2,3,4,5. I want to do a union on these. (I have
done so successfully if I stop here) I also want to join table C which
has field 1,6,7,8,9. I would like to join on field 1 and bring in the
other fields. I can join table C to A or B. I can union table A and B
but I do not know how to both union A and B then join C. Can someone
please help me? Thanks in advance.

What stops you from joining twice? Can't you just do

select 1,2,3,4,5,6,7,8 ,9
from tab_a, tab_c
where tab_a.1 = tab_c.1
union all
select 1,2,3,4,5
from tab_b, tab_c
where tab_b.1 = tab_c.1

Alternatively

select 1,2,3,4,5,6,7,8 ,9
from (
select 1,2,3,4,5
from tab_a
union all
select 1,2,3,4,5
from tab_b
) unioned, tab_c
where unioned.1 = tab_c.1
....

Of course you can also create a view for the union and then another one
for the join - this might be more easier to manage and handle. My guess
would be that the first approach is more efficient but this depends of
course on your data.

HTH

robert

[jason.langdale@gmail.com]

Re: Using Union and Join in a single view


Robert Klemme wrote:

On 08.08.2006 16:31, jason.langdale@ gmail.com wrote:

I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5
and table B has field 1,2,3,4,5. I want to do a union on these. (I have
done so successfully if I stop here) I also want to join table C which
has field 1,6,7,8,9. I would like to join on field 1 and bring in the
other fields. I can join table C to A or B. I can union table A and B
but I do not know how to both union A and B then join C. Can someone
please help me? Thanks in advance.

>
What stops you from joining twice? Can't you just do
>
select 1,2,3,4,5,6,7,8 ,9
from tab_a, tab_c
where tab_a.1 = tab_c.1
union all
select 1,2,3,4,5
from tab_b, tab_c
where tab_b.1 = tab_c.1
>
Alternatively
>
select 1,2,3,4,5,6,7,8 ,9
from (
select 1,2,3,4,5
from tab_a
union all
select 1,2,3,4,5
from tab_b
) unioned, tab_c
where unioned.1 = tab_c.1
...
>
Of course you can also create a view for the union and then another one
for the join - this might be more easier to manage and handle. My guess
would be that the first approach is more efficient but this depends of
course on your data.
>
HTH
>
robert

Thanks for your help. I have rewritten my query but I get an error. It
might be a basic error but I am a Crystal report designer who suddenly
has to create his own views. Please let me know what you think :)

ERROR:
Server: Msg 8156, Level 16, State 1, Procedure historic_sales_ 2, Line 2
The column 'SOPNUMBE' was specified multiple times for
'historic_sales _2'.
SQL VIEW:
CREATE VIEW [historic_sales_ 2] as
SELECT
SOP30200.SOPNUM BE,SOP10200.SOP NUMBE,SOP30200. DOCDATE,SOP3020 0.MSTRNUMB,SOP3 0200.CUSTNMBR,S OP30200.CUSTNAM E,SOP30200.DOCA MNT,SOP30200.LO CNCODE,SOP30200 .VOIDSTTS,SOP30 200.SOPTYPE,SOP 10200.ITEMNMBR, SOP10200.QUANTI TY,SOP10200.UNI TPRCE,SOP10200. XTNDPRCE,SOP102 00.SLPRSNID,SOP 10200.SALSTERR

FROM cvi.dbo.SOP3020 0,cvi.dbo.SOP10 200
WHERE SOP30200.SOPNUM BE = SOP10200.SOPNUM BE
UNION ALL
SELECT

SOP10100.SOPNUM BE,SOP10100.DOC DATE,SOP10100.M STRNUMB,SOP1010 0.CUSTNMBR,SOP1 0100.CUSTNAME,S OP10100.DOCAMNT ,

SOP10100.LOCNCO DE,SOP10100.VOI DSTTS,SOP10100. SOPTYPE
FROM cvi.dbo.SOP1010 0,cvi.dbo.SOP10 200
WHERE SOP10100.SOPNUM BE = SOP10200.SOPNUM BE

[Erland Sommarskog]

Re: Using Union and Join in a single view

jason.langdale@ gmail.com (jason.langdale @gmail.com) writes:

Thanks for your help. I have rewritten my query but I get an error. It
might be a basic error but I am a Crystal report designer who suddenly
has to create his own views. Please let me know what you think :)
>
ERROR:
Server: Msg 8156, Level 16, State 1, Procedure historic_sales_ 2, Line 2
The column 'SOPNUMBE' was specified multiple times for
'historic_sales _2'.

Did you actually try to read the error message? :-) Yes, I realise that
you are not an SQL Server guy, but an error message cannot be much clearer
than this. The column names in a view must be unique, and you have two
columns called SOPNUMBE. Since you join over these column, they obviously
have the same contents, so why include them twice.

By the way, it looks to me that you have a different number of columns in
the two SELECT. That is not going to work out well.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[jason.langdale@gmail.com]

Re: Using Union and Join in a single view

Yes I did read the error message and it's as clear to me as it is to
you. What is not clear to me is how to remedy it. Unfortunately, the
same data is not contained in these two columns either. I don't know if
anyone here knows anything about Microsoft Dynamics GP, which is where
these tables are coming from, but all three tables contain the same
SOPNUMBE column but the rows in each table are unique. One his a
historical table the other two are god knows what. 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. Maybe my entire approach is
wrong. Have you got any ideas?


Erland Sommarskog wrote:

jason.langdale@ gmail.com (jason.langdale @gmail.com) writes:

Thanks for your help. I have rewritten my query but I get an error. It
might be a basic error but I am a Crystal report designer who suddenly
has to create his own views. Please let me know what you think :)

ERROR:
Server: Msg 8156, Level 16, State 1, Procedure historic_sales_ 2, Line 2
The column 'SOPNUMBE' was specified multiple times for
'historic_sales _2'.

>
Did you actually try to read the error message? :-) Yes, I realise that
you are not an SQL Server guy, but an error message cannot be much clearer
than this. The column names in a view must be unique, and you have two
columns called SOPNUMBE. Since you join over these column, they obviously
have the same contents, so why include them twice.
>
By the way, it looks to me that you have a different number of columns in
the two SELECT. That is not going to work out well.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[blueghost73@yahoo.com]

Re: Using Union and Join in a single view

SQL lets you alias your field name, so that's the way to avoid
duplicates. If you have to pull SOPNUMBE from two different places,
just be sure to clearly define which is which, and give each one a
different alias. So if the field was in two tables called TABLE1 and
TABLE2, you could have something like this in your SELECT statement:

SELECT TABLE1.SOPNUMBE AS SOPNUMA, TABLE2.SOPNUMBE AS SOPNUMB ...

--Richard

jason.langdale@ gmail.com wrote:

Yes I did read the error message and it's as clear to me as it is to
you. What is not clear to me is how to remedy it. Unfortunately, the
same data is not contained in these two columns either. I don't know if
anyone here knows anything about Microsoft Dynamics GP, which is where
these tables are coming from, but all three tables contain the same
SOPNUMBE column but the rows in each table are unique. One his a
historical table the other two are god knows what. 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. Maybe my entire approach is
wrong. Have you got any ideas?
>
>
Erland Sommarskog wrote:

jason.langdale@ gmail.com (jason.langdale @gmail.com) writes:

Thanks for your help. I have rewritten my query but I get an error. It
might be a basic error but I am a Crystal report designer who suddenly
has to create his own views. Please let me know what you think :)
>
ERROR:
Server: Msg 8156, Level 16, State 1, Procedure historic_sales_ 2, Line 2
The column 'SOPNUMBE' was specified multiple times for
'historic_sales _2'.

Did you actually try to read the error message? :-) Yes, I realise that
you are not an SQL Server guy, but an error message cannot be much clearer
than this. The column names in a view must be unique, and you have two
columns called SOPNUMBE. Since you join over these column, they obviously
have the same contents, so why include them twice.

By the way, it looks to me that you have a different number of columns in
the two SELECT. That is not going to work out well.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ed Murphy]

Re: Using Union and Join in a single view

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

>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.SOPNUM BE,
SOP30200.DOCDAT E,
SOP30200.MSTRNU MB,
SOP30200.CUSTNM BR,
SOP30200.CUSTNA ME,
SOP30200.DOCAMN T,
SOP30200.LOCNCO DE,
SOP30200.VOIDST TS,
SOP30200.SOPTYP E,
SOP10200.ITEMNM BR,
SOP10200.QUANTI TY,
SOP10200.UNITPR CE,
SOP10200.XTNDPR CE,
SOP10200.SLPRSN ID,
SOP10200.SALSTE RR
FROM cvi.dbo.SOP3020 0,cvi.dbo.SOP10 200
WHERE SOP30200.SOPNUM BE = SOP10200.SOPNUM BE
UNION ALL
SELECT
SOP10100.SOPNUM BE,
SOP10100.DOCDAT E,
SOP10100.MSTRNU MB,
SOP10100.CUSTNM BR,
SOP10100.CUSTNA ME,
SOP10100.DOCAMN T,
SOP10100.LOCNCO DE,
SOP10100.VOIDST TS,
SOP10100.SOPTYP E,
NULL AS ITEMNMBR,
NULL AS QUANTITY,
NULL AS UNITPRCE,
NULL AS XTNDPRCE,
NULL AS SLPRSNID,
NULL AS SALSTERR
FROM cvi.dbo.SOP1010 0,cvi.dbo.SOP10 200
WHERE SOP10100.SOPNUM BE = SOP10200.SOPNUM BE

[Erland Sommarskog]

Re: Using Union and Join in a single view

jason.langdale@ gmail.com (jason.langdale @gmail.com) writes:

Yes I did read the error message and it's as clear to me as it is to
you. What is not clear to me is how to remedy it. Unfortunately, the
same data is not contained in these two columns either.

Since you join over them they are. Or they are not, and you don't get
any rows back at all.

I don't know if anyone here knows anything about Microsoft Dynamics GP,
which is where these tables are coming from, but all three tables
contain the same SOPNUMBE column but the rows in each table are unique.
One his a historical table the other two are god knows what. 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. Maybe my entire approach is
wrong. Have you got any ideas?

I don't know Microsoft Dynamics GP, I don't know what your specifications,
so my prospects to say whether you query will give you want you want are
utterly bleak.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx