"so it appears to be overlapping but is really just a tiled illusion"
Correct - that video is of the animated water tile workaround I mentioned earlier..it doesn't actually use transparent water sprites or anything.
What you're not noticing though is how the checker pattern on the tiles is wiped out and replaced with solid blue. It's not just a transparent overlay.
<img src="https://dl.dropbox.com/u/105711543/Images/Mask2.png" border="0" />
Your example would result in something like the one on the left. I'm going for the one on the right and the goal is to use only 2 objects to reproduce the effect in this image.