aspose file tools*
The moose likes JDBC and the fly likes Simplifying a right outer join Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Simplifying a right outer join" Watch "Simplifying a right outer join" New topic
Author

Simplifying a right outer join

Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9053
    
  12


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
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31062
    
232

Marilyn,
You may be able to do something database specific. For example, in Oracle you can add rownum <=1 to the clause.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9053
    
  12
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

Joined: Feb 05, 2003
Posts: 4727

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
Sheriff

Joined: Jul 22, 2000
Posts: 9053
    
  12
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

Joined: Feb 05, 2003
Posts: 4727

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 ]
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Simplifying a right outer join