Newbie SQL joining question: houses, refrigerators, and apples

Question

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!

Answer 1

SELECT a.*
FROM apples a
INNER JOIN fridge f
ON f.appleID = a.appleID
WHERE a.houseID <> f.houseID

Of course, this is a bad schema design, but you seem to know that already.

Answer 2

This will help you: A Visual Explanation of SQL Joins. It's all there.

Answer 3

select a.*
   from apples a 
   inner join refrigerators r
   on a.refrigeratorid = r.refrigeratorid
   where a.houseid != r.houseid