-1

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.

Bunny
  • 3
  • 1

1 Answers1

2

It seems pretty straight forward, you just need to make a self-join based on condition that one s# matches h# in the other table but not the other way around.

SELECT 
  h1.h#
  ,h1.name
FROM 
  humans AS h1
    JOIN humans AS h2
      ON h1.s# = h2.h# AND h2.s# <> h1.h#
Jakub Kania
  • 15,665
  • 2
  • 37
  • 47
  • That gives me Cindy, but not David and Ellen who are also mismatched. – Bunny Sep 14 '14 at 11:15
  • 1
    No they are not. Your question was "Display humans whose spouse is married to somebody else". David is married to Ellen and Ellen is not married to anyone so she is not married to somebody else. – Jakub Kania Sep 14 '14 at 11:32