When I count distinct psm1.number, I get about 25% fewer rows. I'm guessing that it is due to the right outer join, but I don't know how to change it or even if it's possible to change it so that I only get one result per psm1.number.
JavaBeginnersFaq "Yesterday is history, tomorrow is a mystery, and today is a gift; that's why they call it the present." Eleanor Roosevelt
If you run this as a select *, does psm1 always have a number or are any of the records null?
A good workman is known by his tools.
Marilyn de Queiroz
Joined: Jul 22, 2000
Thanks, Marc. The problem is that I'm getting duplicate psm1.number results, but ... I think I got the idea you were aiming at. All I need to do now is figure out a way to determine which of the multiple relation table rows I really want because there is a 1:many relationship between relation.source and relation.depend.
instead of the joins you could do something like: select count(psm1.number) from psm1 where exists(select 1 from rcause, relation where relation.depend = rcause.id and relation.source = psm1.number)
My syntax might not be exactly what you need but I think you'll understand what I'm trying to do. Your count doesn't require you to return the data from all the tables and it seems you only want to know how many psm1s have a given condition. [ February 07, 2007: Message edited by: Marc Peabody ]