• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Simplifying a right outer join

 
Marilyn de Queiroz
Sheriff
Posts: 9066
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34856
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Marilyn,
You may be able to do something database specific. For example, in Oracle you can add rownum <=1 to the clause.
 
Marilyn de Queiroz
Sheriff
Posts: 9066
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, but I don't necessarily want the first result with that "number". I think I'm trying to break it down so I can see the results of ... oh ... say ... psm1 left outer join rcause on ???
 
Marc Peabody
pie sneak
Sheriff
Posts: 4727
Mac Ruby VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you run this as a select *, does psm1 always have a number or are any of the records null?
 
Marilyn de Queiroz
Sheriff
Posts: 9066
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Marc Peabody
pie sneak
Sheriff
Posts: 4727
Mac Ruby VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic