459,276 Members | 1,311 Online
Need help? Post your question and get tips & solutions from a community of 459,276 IT Pros & Developers. It's quick & easy.

# Using Union and Join in a single view

 P: n/a 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. Aug 8 '06 #1
7 Replies

 P: n/a 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 Aug 8 '06 #2

 P: n/a 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 Aug 8 '06 #3

 P: n/a 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 Aug 8 '06 #4

 P: n/a 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 Aug 9 '06 #5

 P: n/a 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 Aug 9 '06 #6

 P: n/a On 9 Aug 2006 05:00:20 -0700, "ja************@gmail.com" all three tables contain the sameSOPNUMBE 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 thesetables does not have all the fields I want to select, I can't do asimple 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 Aug 9 '06 #7

 P: n/a 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 Aug 9 '06 #8

### This discussion thread is closed

Replies have been disabled for this discussion.