Select distinct

Hi! I have 4 tables and they have a common column (eg. regionid). These
4 tables have data overlapping with the others. Some data exist in a table
but not on the others. What I want to do is to do a select that will display
all distinct regionid from these tables. It should be total of all the tables but will suppress any duplicates with the others.

Note that UNION is working but I can't use that. Why ? because UNION is not supported or maybe not working properly with RDB database. I'm doing an appliaction for heterogenous datasource.

Any tips, hints or info will be appreciated.
thanks in advance.

zrxowm


Table REGION1 :
RegionID RegionDescription
----------- --------------------------------------------------
10 Place1
11 Place11
1 Eastern
2 Western
3 Northern
4 Southern
(6 row(s) affected)

Table REGION2 :
RegionID RegionDescription
----------- --------------------------------------------------
21 Place21
22 Place22
1 Eastern
2 Western
3 Northern
4 Southern
(6 row(s) affected)

Table REGION3 :
RegionID RegionDescription
----------- --------------------------------------------------
33 Place33
31 Place31
1 Eastern
2 Western
3 Northern
4 Southern

(6 row(s) affected)

Table REGION4 :
RegionID RegionDescription
----------- --------------------------------------------------
41 Place41
42 Place42
1 Eastern
2 Western
3 Northern
4 Southern

(6 row(s) affected)

select regionid,regiondescription into @temp1 from region1
select regionid,regiondescription into @temp2 from region2
select regionid,regiondescription into @temp3 from region3
select regionid,regiondescription into @temp4 from region4

select regionid,regiondescription from @temp1
union
select regionid,regiondescription from @temp2
union
select regionid,regiondescription from @temp3
union
select regionid,regiondescription from @temp4





------------
zrxowm at 2/15/00 4:20:57 PM

Hi! I have 4 tables and they have a common column (eg. regionid). These
4 tables have data overlapping with the others. Some data exist in a table
but not on the others. What I want to do is to do a select that will display
all distinct regionid from these tables. It should be total of all the tables but will suppress any duplicates with the others.

Note that UNION is working but I can't use that. Why ? because UNION is not supported or maybe not working properly with RDB database. I'm doing an appliaction for heterogenous datasource.

Any tips, hints or info will be appreciated.
thanks in advance.

zrxowm


Table REGION1 :
RegionID RegionDescription
----------- --------------------------------------------------
10 Place1
11 Place11
1 Eastern
2 Western
3 Northern
4 Southern
(6 row(s) affected)

Table REGION2 :
RegionID RegionDescription
----------- --------------------------------------------------
21 Place21
22 Place22
1 Eastern
2 Western
3 Northern
4 Southern
(6 row(s) affected)

Table REGION3 :
RegionID RegionDescription
----------- --------------------------------------------------
33 Place33
31 Place31
1 Eastern
2 Western
3 Northern
4 Southern

(6 row(s) affected)

Table REGION4 :
RegionID RegionDescription
----------- --------------------------------------------------
41 Place41
42 Place42
1 Eastern
2 Western
3 Northern
4 Southern

(6 row(s) affected)

as what i've said, they are heterogenous datasource. one table from oracle,one for sybase, one for ms sql server and one for rdb.
when we ran the union for the first three it is working fine and when we included the rdb it hang the system. it looks like union is not supported on rdb. the tables are just an example. the real data may consists of hundreds of thousands if not millions of data, so putting it on a temp table will definitely degrade the system. what we're thinking if we could use a complex sql statement without using any cursor or temp table.
if you have any more thoughts on this, please share it . I appreciate it.

Thanks for all the replies. I really appreciated it.
zrxowm


------------
Cindy Gross at 2/16/00 1:33:20 PM

My first thought would be to have only one table instead of four. What is the difference between them? Would a composite key allow them to be in one table and still meet the criteria of "this table has one row per region/whatever you're tracking"?

If they do truly belong in separate tables, and you can't use UNION, then you could do:
create table #region (region int not null)
insert #region select region from region1
insert #region select region from region2
--where region not in (select region from #region)
insert #region select region from region3
--where region not in (select region from #region)
insert #region select region from region4
--where region not in (select region from #region)

select distinct region from #region

-- depending on the size of the region tables and how many rows you plan to pull into #region it may or may not be better to add the where clauses in.

Cindy
http://cindygross.tripod.com

------------
Zhong Hong at 2/15/00 5:37:20 PM

select regionid,regiondescription into @temp1 from region1
select regionid,regiondescription into @temp2 from region2
select regionid,regiondescription into @temp3 from region3
select regionid,regiondescription into @temp4 from region4

select regionid,regiondescription from @temp1
union
select regionid,regiondescription from @temp2
union
select regionid,regiondescription from @temp3
union
select regionid,regiondescription from @temp4





------------
zrxowm at 2/15/00 4:20:57 PM

Hi! I have 4 tables and they have a common column (eg. regionid). These
4 tables have data overlapping with the others. Some data exist in a table
but not on the others. What I want to do is to do a select that will display
all distinct regionid from these tables. It should be total of all the tables but will suppress any duplicates with the others.

Note that UNION is working but I can't use that. Why ? because UNION is not supported or maybe not working properly with RDB database. I'm doing an appliaction for heterogenous datasource.

Any tips, hints or info will be appreciated.
thanks in advance.

zrxowm


Table REGION1 :
RegionID RegionDescription
----------- --------------------------------------------------
10 Place1
11 Place11
1 Eastern
2 Western
3 Northern
4 Southern
(6 row(s) affected)

Table REGION2 :
RegionID RegionDescription
----------- --------------------------------------------------
21 Place21
22 Place22
1 Eastern
2 Western
3 Northern
4 Southern
(6 row(s) affected)

Table REGION3 :
RegionID RegionDescription
----------- --------------------------------------------------
33 Place33
31 Place31
1 Eastern
2 Western
3 Northern
4 Southern

(6 row(s) affected)

Table REGION4 :
RegionID RegionDescription
----------- --------------------------------------------------
41 Place41
42 Place42
1 Eastern
2 Western
3 Northern
4 Southern

(6 row(s) affected)