I am a student taking an Oracle database class and for the life of me I cannot figure out this problem. I would really appreciate if you could help.
Each human has one spouse, who is also a human.
Table name: Humans
Records:
- H# HName S#
- 101 Adam 102
- 102 Eve 101
- 103 Cindy 104
- 104 David 105
- 105 Ellen null
- 106 Frank 107
- 107 George 106
H#=Primary Key, S#=Foreign Key
Question: "Display humans whose spouse is married to somebody else. For instance, if 101’s spouse is 102, then 102’s spouse should be 101. However, in this table, 103’s spouse is 104, but 104’s spouse is 105. Write a query to find all records of such mis-matches."
I know the question requires a self-join but all the self-joins I create either return all records or return nothing.