Does the SCSI controller have a 'real' hardware RAID controller? In a "real" RAID controller, the drives are invisible to the OS. The OS sees the RAID. It sounds like you have a SCSI controller that does not have BIOS level hooks to boot. Is this correct?
Attached to the SCSI controller is an 80GB drive. Right?
Attached to another BIOS recognized controller is a 40GB drive. Right?
http://www.supergrubdisk.org/ Be aware that the power of supergrubdisk really doesn't begin to be understood until you get a good understanding a Linux boot process and GRUB. I learned quite a bit sorting out a problem very similar to yours.
SystemRescueCD is easier to get a system to boot... IF it can sort out your partitions.
It sounds to me like the boot partition is set to the SCSI drive and for whatever reason the root of that drive can't be loaded.