horizontal union operation
-
I have two irrelavent Microsoft Access tables. They have the same number of records but no other relations. Is there a way to create a new table using SQL with fields from the two source tables put together? For example: Table1:
Name Address
John 302 ABC Street
Smith 412 DEF StreetTable2:
Phone email
123-4567 m1@hotmail.com
234-5678 s1@gmail.comCan I use a simple SQL command to create: Table3:
Name Address Phone email
John 302 ABC Street 123-4567 m1@hotmail.com
Smith 412 DEF Street 234-5678 s1@gmail.comI was thinking of using a join operation but the two source tables does not have any relations, and I can't join the table by record numbers. The only thing that's guaranteed is that the two source tables have the same amount of records. Is there any way to achieve this just by using SQL commands without resort to writing a custom program? Thanks!
-
I have two irrelavent Microsoft Access tables. They have the same number of records but no other relations. Is there a way to create a new table using SQL with fields from the two source tables put together? For example: Table1:
Name Address
John 302 ABC Street
Smith 412 DEF StreetTable2:
Phone email
123-4567 m1@hotmail.com
234-5678 s1@gmail.comCan I use a simple SQL command to create: Table3:
Name Address Phone email
John 302 ABC Street 123-4567 m1@hotmail.com
Smith 412 DEF Street 234-5678 s1@gmail.comI was thinking of using a join operation but the two source tables does not have any relations, and I can't join the table by record numbers. The only thing that's guaranteed is that the two source tables have the same amount of records. Is there any way to achieve this just by using SQL commands without resort to writing a custom program? Thanks!
I'm wondering what you'd do if you had to 'resort to writing a custom program?' - since as you say, they are (I'll use the terms) disjoint/un-related instead of your 'irrelavent' since you dont have anything in common between the tables, and as you say, you cant join by record numbers the only way I can see of doing this (which assumes you can see record numbers) is to manually create a 'link' table with two columns, record-number-table-1, record-number-table-2, and manually enter the pairs of corresponding record numbers into it using 'eyeball matching' and 'insert' statements - of course, if you have a truckload of data this is going to take a while. You then select record from each table based on the rows in the 'link' table There must be more to the situation than you're showing us here - To 'write a custom program' implies you know of some relationship between the tables/data, but just because they 'have the same amount of records' is a bad assumption - what about duplicates for example ? 'g'
-
I have two irrelavent Microsoft Access tables. They have the same number of records but no other relations. Is there a way to create a new table using SQL with fields from the two source tables put together? For example: Table1:
Name Address
John 302 ABC Street
Smith 412 DEF StreetTable2:
Phone email
123-4567 m1@hotmail.com
234-5678 s1@gmail.comCan I use a simple SQL command to create: Table3:
Name Address Phone email
John 302 ABC Street 123-4567 m1@hotmail.com
Smith 412 DEF Street 234-5678 s1@gmail.comI was thinking of using a join operation but the two source tables does not have any relations, and I can't join the table by record numbers. The only thing that's guaranteed is that the two source tables have the same amount of records. Is there any way to achieve this just by using SQL commands without resort to writing a custom program? Thanks!
Hi, if everything else fails, you could add a field to both tables ("ID") and fill it with sequential numbers. Then join both tables on the new field. :)
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
I have two irrelavent Microsoft Access tables. They have the same number of records but no other relations. Is there a way to create a new table using SQL with fields from the two source tables put together? For example: Table1:
Name Address
John 302 ABC Street
Smith 412 DEF StreetTable2:
Phone email
123-4567 m1@hotmail.com
234-5678 s1@gmail.comCan I use a simple SQL command to create: Table3:
Name Address Phone email
John 302 ABC Street 123-4567 m1@hotmail.com
Smith 412 DEF Street 234-5678 s1@gmail.comI was thinking of using a join operation but the two source tables does not have any relations, and I can't join the table by record numbers. The only thing that's guaranteed is that the two source tables have the same amount of records. Is there any way to achieve this just by using SQL commands without resort to writing a custom program? Thanks!
Try this declare @tbl1 table(name varchar(20), address varchar(50)) insert into @tbl1 select 'John','302 ABC Street' union all select 'Smith','412 DEF Street' declare @tbl2 table(phone varchar(20), email varchar(50)) insert into @tbl2 select '123-4567','m1@hotmail.com' union all select '234-5678','s1@gmail.com'
;with cte1 as (select t1.*, ROW_NUMBER()over (order by name) as rn from @tbl1 t1)
,cte2 as (select t2.*, ROW_NUMBER()over (order by phone) as rn from @tbl2 t2)
select c1.name,c1.address,c2.phone, c2.email
from cte1 c1
inner join cte2 c2
on c1.rn = c2.rnThe output is
name address phone email
John 302 ABC Street 123-4567 m1@hotmail.com
Smith 412 DEF Street 234-5678 s1@gmail.comBut ur database design is wrong.. normalize that please. I use the Row_number() function to accomplish the task.... :)
Niladri Biswas
-
I have two irrelavent Microsoft Access tables. They have the same number of records but no other relations. Is there a way to create a new table using SQL with fields from the two source tables put together? For example: Table1:
Name Address
John 302 ABC Street
Smith 412 DEF StreetTable2:
Phone email
123-4567 m1@hotmail.com
234-5678 s1@gmail.comCan I use a simple SQL command to create: Table3:
Name Address Phone email
John 302 ABC Street 123-4567 m1@hotmail.com
Smith 412 DEF Street 234-5678 s1@gmail.comI was thinking of using a join operation but the two source tables does not have any relations, and I can't join the table by record numbers. The only thing that's guaranteed is that the two source tables have the same amount of records. Is there any way to achieve this just by using SQL commands without resort to writing a custom program? Thanks!
First of all, thanks for all who replied. I was given those Access tables and was asked to do that "horizontal join". I did not design the database, so I have no control over the design of the tables. For example, there is no primary key in those tables. Had I designed them, I would probably put a rowid field there as the primary key. The person who asked me to do this needed the result table "so that he can load the table in GeoMedia to map the points." I don't know the details about GeoMedia. I just do whatever he asks me to do. He is my boss. The real requirement is more than what I presented here. He actually asked me to horizontally join more than two tables. Actually the number of tables are unknown at design time. I am actually writing a program to do this. However, I want a query to simplify my program. I was hoping that I can avoid the result table creation part because I don't want to deal with Access data types. If a query is possible, I will just use "select ... into ..." to create the result table, instead of using "create table ..." because to construct the create command I have to find out the fields' info (name and type) for the result table. Thanks again for all your replies.
-
Try this declare @tbl1 table(name varchar(20), address varchar(50)) insert into @tbl1 select 'John','302 ABC Street' union all select 'Smith','412 DEF Street' declare @tbl2 table(phone varchar(20), email varchar(50)) insert into @tbl2 select '123-4567','m1@hotmail.com' union all select '234-5678','s1@gmail.com'
;with cte1 as (select t1.*, ROW_NUMBER()over (order by name) as rn from @tbl1 t1)
,cte2 as (select t2.*, ROW_NUMBER()over (order by phone) as rn from @tbl2 t2)
select c1.name,c1.address,c2.phone, c2.email
from cte1 c1
inner join cte2 c2
on c1.rn = c2.rnThe output is
name address phone email
John 302 ABC Street 123-4567 m1@hotmail.com
Smith 412 DEF Street 234-5678 s1@gmail.comBut ur database design is wrong.. normalize that please. I use the Row_number() function to accomplish the task.... :)
Niladri Biswas
-
I have two irrelavent Microsoft Access tables. They have the same number of records but no other relations. Is there a way to create a new table using SQL with fields from the two source tables put together? For example: Table1:
Name Address
John 302 ABC Street
Smith 412 DEF StreetTable2:
Phone email
123-4567 m1@hotmail.com
234-5678 s1@gmail.comCan I use a simple SQL command to create: Table3:
Name Address Phone email
John 302 ABC Street 123-4567 m1@hotmail.com
Smith 412 DEF Street 234-5678 s1@gmail.comI was thinking of using a join operation but the two source tables does not have any relations, and I can't join the table by record numbers. The only thing that's guaranteed is that the two source tables have the same amount of records. Is there any way to achieve this just by using SQL commands without resort to writing a custom program? Thanks!