code_junkie

Query three non-bisecting sets of data

Nov 13th, 2011
92
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.12 KB | None | 0 0
  1. SELECT DISTINCT t1.*
  2. FROM table1 t1
  3. INNER JOIN table2 t2
  4. ON t2.ID = t1.ID
  5. AND t2.NAME = t1.NAME
  6. AND t2.ADDRESS <> t1.ADDRESS
  7.  
  8.  
  9. SELECT DISTINCT t1.*
  10. FROM table1 t1
  11. INNER JOIN table2 t2
  12. ON t2.ID = t1.ID
  13. AND t2.NAME <> t1.NAME
  14. AND t2.ADDRESS <> t1.ADDRESS
  15.  
  16.  
  17. SELECT DISTINCT t1.*
  18. FROM table1 t1
  19. INNER JOIN table2 t2
  20. ON t2.ID <> t1.ID
  21. AND t2.NAME = t1.NAME
  22. AND t2.ADDRESS <> t1.ADDRESS
  23.  
  24. Table 1 Table 2
  25. ID Name Address ID Name Address
  26. 0 Jim Smith 1111 A St 0 Jim Smith 2222 A St
  27. 1 Jim Smith 2222 B St 1 Jim Smith 3333 C St
  28.  
  29. 0 Jim Smith 1111 A St
  30. 1 Jim Smith 2222 B St
  31.  
  32. 0 Jim Smith 1111 A St
  33. 1 Jim Smith 2222 B St
  34.  
  35. SELECT DISTINCT t1.*
  36. FROM table1 t1
  37. INNER JOIN table2 t2
  38. ON t2.ID = t1.ID
  39. AND t2.NAME = t1.NAME
  40. AND t2.ADDRESS <> t1.ADDRESS
  41. INTERSECT
  42. SELECT DISTINCT t1.*
  43. FROM table1 t1
  44. INNER JOIN table2 t2
  45. ON t2.ID = t1.ID
  46. AND t2.NAME <> t1.NAME
  47. AND t2.ADDRESS <> t1.ADDRESS
Add Comment
Please, Sign In to add comment