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
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.
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 ]
This will take every ounce of my mental strength! All for a tiny ad:
free, earth-friendly heat - a kickstarter for putting coin in your pocket while saving the earth