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: 9043
    
  10


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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29261
    
140

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: 9043
    
  10
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: 9043
    
  10
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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Simplifying a right outer join
 
Similar Threads
counting rows after joins varies
Old style SQL vs new style SQL
problem with "group by"
comparing records
Right Outer Join in Oracle