Newbie SQL joining question: houses, refrigerators, and apples
- by Philip Brocoum
There are three tables in my database: apples, refrigerators, and houses. Each apple belongs to a refrigerator (it has a column that references the refrigerator that it's in), and each refrigerator belongs to a house. The problem is, each apple ALSO belongs to a house. I guess this isn't "normalized" or whatever, but it's the way it is.
Obviously, each apple should belong to the house that its refrigerator belongs to. An apple can't be in two different places at once, after all.
So, my question is this: what SQL statement will allow me to find all of the "bad apples" that for some reason incorrectly reference a different house than the refrigerator that they are supposedly in. In other words, I need to find the apples that contain contradictory information.
I don't know if this is a "join" question or not, but I'm sure there's probably a very straightforward way of figuring this out. I just don't know what it is. Thanks for your help!