0

I searched though the internet but could not found what i was looking for, may be because i dont know what to search.

My problem is to get non-duplicate records from multiple tables...

Say if i have two tables as follows,

tableA
----------------------
code    ip
1       111.168.1.2
2       111.222.333.333
3       111.168.1.2
4       111.234.2.44
5       111.23.455.44

tableB
----------------------
code    ip                 ISP
1       111.222.333.333    newtwo
2       111.168.2.2        anon
3       111.333.453.567    def
4       111.168.2.2        tele
5       111.222.333.333    new
6       111.333.544.3      ispnew

What i expect to get out of this is,

ip
111.234.2.44
111.23.455.44
111.333.453.567
143.333.544.3

I have tried UNION, DISTICNT and DISTINCT AND UNION together like so,

SELECT DISTINCT IP FROM(SELECT IP FROM tableA UNION SELECT IP FROM tableB)

But it gives all the values unique an duplicate as follows,

ip
111168.1.2
111.222.333.333
111.234.2.44
111.23.455.44
111.168.2.2 
111.333.453.567     
111.333.544.3

Please help me on this one, I will be much great-full..THANK YOU VERY MUCH :)

Hasitha Shan
  • 2,900
  • 6
  • 42
  • 83

1 Answers1

4

DISTINCT give every entry just once but doesn't throw out the duplicate ones completely. what you'll have to to is using GROUP BY, COUNT and HAVING to get rid of those:

SELECT
  IP
FROM
  (
    SELECT IP FROM tableA
  UNION ALL
    SELECT IP FROM tableB
  )
GROUP BY
  IP
HAVING
  COUNT(*) = 1
oezi
  • 51,017
  • 10
  • 98
  • 115
  • Got me by a bunch of seconds :'( – Olivier Coilland Aug 28 '12 at 11:05
  • yessss..thankyou very much!!!! it works!! :) this is exactly what i was looking for :) il accept it as the answer in 3 minutes, dt what was said when i tried to mark it :) – Hasitha Shan Aug 28 '12 at 11:10
  • Hey..sorry for bothering, may i know if i could speed up this process...i have lyk 6000 records to be matched and this takes some time :) thank a bunch :) – Hasitha Shan Aug 28 '12 at 11:19
  • I don't think there is any further scope for optimizing this query. `UNION ALL` instead of `UNION` makes it faster. Still you can add `ORDER BY NULL` clause in this query. – Omesh Aug 28 '12 at 11:33
  • okey.. thank you very much for your reply.ill try those out..very much appreciated :) – Hasitha Shan Aug 28 '12 at 13:24