Using Union and Join in a single view

[jason.langdale]

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]

On 08.08.2006 16:31, ja************@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]

Robert Klemme wrote:

On 08.08.2006 16:31, ja************@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.SOPNUMBE,SOP10200.SOPNUMBE,SOP30200.DOCDA TE,SOP30200.MSTRNUMB,SOP30200.CUSTNMBR,SOP30200.CU STNAME,SOP30200.DOCAMNT,SOP30200.LOCNCODE,SOP30200 .VOIDSTTS,SOP30200.SOPTYPE,SOP10200.ITEMNMBR,SOP10 200.QUANTITY,SOP10200.UNITPRCE,SOP10200.XTNDPRCE,S OP10200.SLPRSNID,SOP10200.SALSTERR

FROM cvi.dbo.SOP30200,cvi.dbo.SOP10200
WHERE SOP30200.SOPNUMBE = SOP10200.SOPNUMBE
UNION ALL
SELECT

SOP10100.SOPNUMBE,SOP10100.DOCDATE,SOP10100.MSTRNU MB,SOP10100.CUSTNMBR,SOP10100.CUSTNAME,SOP10100.DO CAMNT,

SOP10100.LOCNCODE,SOP10100.VOIDSTTS,SOP10100.SOPTY PE
FROM cvi.dbo.SOP10100,cvi.dbo.SOP10200
WHERE SOP10100.SOPNUMBE = SOP10200.SOPNUMBE

[Erland Sommarskog]

ja************@gmail.com (ja************@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, es****@sommarskog.se

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

[jason.langdale]

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:

ja************@gmail.com (ja************@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, es****@sommarskog.se

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

[blueghost73]

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

ja************@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:

ja************@gmail.com (ja************@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, es****@sommarskog.se

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

[Ed Murphy]

On 9 Aug 2006 05:00:20 -0700, "ja************@gmail.com"
<ja************@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.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

[Erland Sommarskog]

ja************@gmail.com (ja************@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, es****@sommarskog.se

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