GeeCON Prague 2014*
The moose likes JDBC and the fly likes counting rows after joins varies Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "counting rows after joins varies" Watch "counting rows after joins varies" New topic
Author

counting rows after joins varies

Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9046
    
  10
I thought that once I did an inner join that the resulting number of rows would be a limiting factor regardless of how many left outer joins I did afterwards.

and that seems to be true for the first few lines of this query


But when I include the last two lines of the above code, the row count increases. I don't understand why.


JavaBeginnersFaq
"Yesterday is history, tomorrow is a mystery, and today is a gift; that's why they call it the present." Eleanor Roosevelt
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
I thought that once I did an inner join that the resulting number of rows would be a limiting factor


No. Adding a left outer join to a query will not decrease the number of rows returned. But it could increase the number or rows returned if the outer join condition results in a one-to-many match.
Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9046
    
  10
Is there any way to make it stay the same number of rows as the table on the left? I know an inner join definitely won't work.
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
Maybe additional join conditions to filter out the unwanted rows...
Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9046
    
  10
Thanks, Scott. Sometimes I just can't see the forest for the trees.
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1747
    
    2
MdQ: counting rows after joins varies

Of course it can!

****

1. Also be extremely mindful of exactly what you're COUNT()ing on in situations such as this (particularly regarding NULLs).

For example, each of the following *can* result in a different count:

select count(*)
from a left join b
on a.id = b.id

select count(a.id)
from a left join b
on a.id = b.id

select count(b.id)
from a left join b
on a.id = b.id

select count(a.some_other_field)
from a left join b
on a.id = b.id

select count(b.yet_even_some_other_field)
from a left join b
on a.id = b.id

2. Be mindful of the fields you're grouping on.

Is there any way to make it stay the same number of rows as the table on the left?

Typically, count on some nonnullable field on the left table (often a key, and often one of the fields you're joining on, but not obligatorily).
[ September 21, 2006: Message edited by: Michael Matola ]
 
GeeCON Prague 2014
 
subject: counting rows after joins varies