UNION and distinct 
Author Message
 UNION and distinct

Hi, I have trouble trying to display a list of distinct rows from the
result of union.  I am trying to union eight tables, all with some money
field, an unique account id field, and a term code field which is not
unique.  I have to display a list all the account ids which have a money
field greater than 0, and with a term code of '002' without displaying
duplicate account id's.  All these tables may or may have same account id
records with different amount of money in the money field, and all
these tables have different fields in them except the two columns,
account id and the money field.  Can anyone show me an elegant example of
how to approach this?  

James



Tue, 22 Apr 2003 03:00:00 GMT
 UNION and distinct

James,

If it was me, I'd extract the data from each of the 8 source tables into
a temp table first:

create table #temp1 (account_id int, money_field money,
term_code char(3))
go

update #temp1
select distinct account_id, money_field, term_code
from source_table_1
go

< do the same for the rest of the source tables, then: >

select distinct account_id, money_field from #temp1
where money_field > 0 and term_code = '002'
go

Hope this helps,

                                 Larry Burns

In article


Quote:

> Hi, I have trouble trying to display a list of distinct rows from the
> result of union.  I am trying to union eight tables, all with some
money
> field, an unique account id field, and a term code field which is not
> unique.  I have to display a list all the account ids which have a
money
> field greater than 0, and with a term code of '002' without displaying
> duplicate account id's.  All these tables may or may have same account
id
> records with different amount of money in the money field, and all
> these tables have different fields in them except the two columns,
> account id and the money field.  Can anyone show me an elegant example
of
> how to approach this?

> James

Sent via Deja.com http://www.deja.com/
Before you buy.


Sat, 26 Apr 2003 08:13:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. UNION and DISTINCT

2. DISTINCT ON () with UNION

3. Getting DISTINCT from a SQL Union Result???

4. DISTINCT in UNION does not work

5. DISTINCT in UNION

6. distinct on a union query.

7. distinct vs union all?

8. UNION and UNION ALL working backwards?????

9. UNION, UNION ALL

10. sort a union, view of union

11. UNION or UNION ALL - which faster

12. Diff. between UNION and UNION ALL


 
Powered by phpBB® Forum Software