-1

I am using union all in my code something like 

select * from country

union all 

select * from city

If same entry is there in both the select, then it should count only once.

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
Ritz
  • 1
  • 1

3 Answers3

3

Use UNION instead:

select * from country

union 

select * from city

This will effectively filter out any duplicate records.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • @Ritz Can your post some sample data demonstrating the issue? – Giorgos Betsos Apr 27 '16 at 06:02
  • i am not able to post my query – Ritz Apr 27 '16 at 06:07
  • 1
    @Ritz I'm not asking you to post the query. I'm asking for some sample *data*, so that we can understand what the *real* issue is. – Giorgos Betsos Apr 27 '16 at 06:10
  • where i can post my query? – Ritz Apr 27 '16 at 06:10
  • I have table called transaction, now i want to retrieve data from it and i have 2 condition 1> when trstype is 400 and trsdate is '27/04/2016' and 2> trstype is 600 and trsdate is '27/04/2016'. But some transaction on the same date is having trstype 400 and trstype 600 , but i want the queryu to count only once if it finds 400 it stops and search the different transaction – Ritz Apr 27 '16 at 06:11
  • @Ritz Just edit the original post and add some data there along with the output the you get. – Giorgos Betsos Apr 27 '16 at 06:11
  • where status='a' and trstypeid in(609) and DepartmentAccountID in (30,31) and (previousprocessid in ('3101')) and NextProcessID=205 and trsdate between '2016-04-26 00:00:01' and '2016-04-26 23:59:59' union all where status='a' and trstypeid in(1002) and DepartmentAccountID in (30,31) and (previousprocessid in (3101,3105)) and NextProcessID=205 and trsdate between '2016-04-26 00:00:01' and '2016-04-26 23:59:59' it is giving me duplicate value for transaction having both trstypeid 609 and 1002 and i want it to populate once – Ritz Apr 27 '16 at 06:18
  • @ritz: ***edit*** your question. Don't post code or additional information in comments. –  Apr 27 '16 at 06:20
  • i am using union all in my query and i want to remove the duplications – Ritz Apr 27 '16 at 06:21
  • 72153615 72153615 i am getting 2 transaction id like this which i want to avoid – Ritz Apr 27 '16 at 06:41
  • 1
    You can't get duplicates if you are using union. What you have might look like duplicates to you but it is not. You are missing something in your data. This is why people want you to show sample data, because then they can point at the two values of the rows that are different. – David Söderlund Apr 27 '16 at 07:18
0

If you do not need duplicate you should use union not union all

select distinct * from country
union
select distinct * from city

edit: You must be getting the duplicate values from country table. Now I've changed my query according to your requirement

Andrews B Anthony
  • 1,381
  • 9
  • 27
0

First of all don't do SELECT * and select only the columns you need. UNION should remove duplicates. 

      select col1, col2 from country
      union
      select col1, col2 from city
Mircea Movila
  • 101
  • 6
  • 1
    Why do duplicate elimination twice? Wont change a thing. – jarlh Apr 27 '16 at 07:09
  • I said he doesn't need the group by because union will remove duplicates but maybe the group by can help the asker. In the example i wrote group by does nothing and CAN be removed – Mircea Movila Apr 27 '16 at 10:30