counting rows after joins varies

[Marilyn de Queiroz]

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

     SCADMIN.probsummarym2 psm2
    INNER JOIN SCADMIN.probsummarym1 psm1 ON psm1.number = psm2.number
    LEFT OUTER JOIN SCADMIN.operatorm1 operm1 ON psm1.assignee_name = operm1.name
    LEFT OUTER JOIN SCADMIN.contactsm1 contactsm1 ON operm1.full_name = contactsm1.contact_name
    LEFT OUTER JOIN SCADMIN.assignmentm1 assignmentm1 ON psm1.assignment = assignmentm1.name
    LEFT OUTER JOIN SCADMIN.operatorm1 operatorm2 ON assignmentm1.wdManagerName = operatorm2.name
    LEFT OUTER JOIN SCADMIN.contactsm1 contactsm2 ON operatorm2.full_name = contactsm2.contact_name
 
    LEFT OUTER JOIN SCADMIN.contactsm1 con1 ON con1.logon_id0_trigon = psm1.opened_by
    LEFT OUTER JOIN SCADMIN.contactsm1 con2 ON con2.logon_id0_trigon = psm1.assignee_name
   

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

[Scott Johnson]

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]

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]

Maybe additional join conditions to filter out the unwanted rows...

[Marilyn de Queiroz]

Thanks, Scott. Sometimes I just can't see the forest for the trees.

[Michael Matola]

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 ]