Data View - Combining Fields from Two Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Data View - Combining Fields from Two Tables

Author

Topic

DavidWe
Starting Member

3 Posts

Posted - 2006-06-23 : 11:27:50

I have two sales order tables - soymst and somast. Soymst contains data for the current quarter up through the last day of the previous month. Right now that would be 9/1/2005 - 5/31/2006. Somast contains data for the current month - June. At the end of each month the history table, soymst, is updated with the current, somast. Both tables have the same field names.

Is there a way to select a field from both tables and give them a single name? For example, both tables have custno. I know how to select custno from both tables and give each one its own alias. Can I select custno from both tables and give them one name like custnoboth as opposed to having custno-A and custno-B in the data view? The dataview would then have one field for the custno taken from both tables. Duplicates are allowed for custno in case that makes a difference.

Thanks.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-23 : 11:50:54

I'm sure this is very easy, but, to clarify what you're after, could you just give a simple example. e.g....

--I have the following tables and data...
declare @soymst table (i int, custno int)
insert @soymst
        select 1, 2

declare @somast table (i int, custno int)
insert @somast
        select 3, 4

/* and I want these results...
i           custno      
----------- ----------- 
1           2
3           4
*/

          select * from @soymst
union all select * from @somast

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

DavidWe
Starting Member

3 Posts

Posted - 2006-06-23 : 13:43:09

Ryan,

Well, I'm using a program called Jet Reports where you drag fields from a table into an Excel spreadsheet. That part of it is hard to explain if you have never used it. But that is the reason for my question on the query.

I'll try to clarify what I am hoping for from the query. I think you understand the two tables that the query will be run on. The resulting table (view) would only have one field for custno, but the custno would have all the custno's from both soymst and somast. When I tried it previously, the resulting view contained separate fields for the custno for each table.

I will try:
select * from @soymst
union all select * from @somast

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-23 : 13:57:55

Okay, David, try that. But if that doesn't work, give some examples in the format I suggested. Examples are easy to understand and to test against

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

DavidWe
Starting Member

3 Posts

Posted - 2006-06-23 : 15:47:14

Thanks, Ryan. I'll see if I can give an example:

soymst:
custno sono sodate
---------------------
10333 3345 4/1/2006
10212 3350 4/12/2006
10676 4422 5/20/2006

somast:
custno sono sodate
----------------------
11323 4533 6/2/2006
12255 4540 6/11/2006

Resulting table or data view:
custno sono sodate
-----------------------
10333 3345 4/1/2006
10212 3350 4/12/2006
10676 4422 5/20/2006
11323 4533 6/2/2006
12255 4540 6/11/2006

Basically, I want soymst.custno and somast.custno to have the same column name in the result. It will actually have more columns. I was just using custno in my question. I added two more in my example. The two starting tables, soymst and somast, have the same column names. I want the resulting table to have the same column names. Some joins will give a different column name for soymst.custno and somast.custno. One other comment: I perhaps should have asked about combining columns instead of field names. Maybe that would have made it clearer.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-23 : 19:53:13

looks like a simple union all to me


select custno, sono, sodate from soymst
union all
select custno, sono, sodate from somast

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources