Geeks Talk - SQL http://www.geekinterview.com/talk/ en Sat, 21 Nov 2009 18:59:15 GMT vBulletin 60 http://www.geekinterview.com/talk/images/misc/rss.jpg Geeks Talk - SQL http://www.geekinterview.com/talk/ How to use union clause to eliminate duplicates http://www.geekinterview.com/talk/16538-how-use-union-clause-eliminate-duplicates.html Sun, 15 Nov 2009 18:40:20 GMT Hello, I have 2 tables. From table 1 the columns which i need to display are Table 1 columns-... Hello,

I have 2 tables.
From table 1 the columns which i need to display are
Table 1 columns- a_name,a_addressa,a_addressb,a_addressc,a_city,a_taluk.a_district,a_pin(all varchar2).
Table has 2859 rows.

From table 2 the columns which i need to display are
Table 2 columns- b_name,b_Add1,b_Add2,b_Add3,b_Add4,b_Add5,b_City,b_pin(all varchar2)
Table 2 has 1036 rows.

So now there are some names which may belong to both the tables.So i need to eliminate that.So based on Name and address i need to eliminate duplicate rows.Because 2 people can have same name also.So just i cant take name.
Even if name and address is same then i want to take only once that data.

I am using the query like this
select a_name, a_addressa||','||a_addressb||','||a_addressc, a_city||','||a_taluk||','||a_dist, a_pin from table_a
union
select b_name, b_add1||','||b_add2||','||b_add3||','||b_add4||','||b_add5, b_city, b_pin from table_b

sample Data in table_a is
a_name=Seema T.N.
a_addressa=No 112/3,Seema House
a_addressb=3 rd Cross,Near FTM bakery
a_addressc=Palam Colony
a_city=Delhi
a_taluk=Delhi
a_dist=Delhi
a_pin=110010

sample Data in table_b is
b_name=Seema T.N.
b_add1=No 112/3
b_add2=Seema House
b_add3=3 rd Cross
b_add4=Near FTM bakery
b_add5=PalamColony
b_city=Delhi
b_pin=110010

Here i am considering a data where same person is present in both the tables.
But still the query which i have written its returns 2 rows.Because while entering the data in table_b,some extra spaces would have been added or some words may be combined,some words may be missed.

Actually it should written me only one row.
But still since i am concatenating the address the duplicates are still coming.

How can i do this? ]]> SQL swapna053 http://www.geekinterview.com/talk/16538-how-use-union-clause-eliminate-duplicates.html