Yes, but only if the two cards use the same core (for example 5870 and 5850) then the faster of the two will clock down to the speed of the slower.
However, the 5970 is a special case since it is a dual GPU card (2 slightly downclocked 5870s on the same PCB), therefore, if you add another 5870, it will clock down slightly to match the two GPUs on the 5970 and you will get tri-fire.