CODEWITHSUNDEEP
sqlsql-server-2005
user2531854
user2531854

Reputation: 866

Incorrect Count with Multiple Joins

I'm getting an incorrect count when I use multiple 'Joins'. It should only show 3 as the total but it's returning 134 for the total. What's the proper way to use COUNT with multiple 'Joins'?

SELECT  r.Field1
      , Total = COUNT(r.Field1)
FROM    Location1.dbo.Table1 r ( NOLOCK )
        JOIN Location2.dbo.Table2 i ( NOLOCK ) ON r.Field1 = i.Field1
        JOIN Location3.dbo.Table3 rt ( NOLOCK ) ON rt.Field1 = i.Field1
                                                   AND rt.Field2 = r.Field2
WHERE   r.Field3 = '40'
        AND r.Field4 = 'H'
        AND r.Field1 = '516'
        AND CONVERT(CHAR(10), r.TIMESTAMP, 101) = CONVERT(CHAR(10), GETDATE(), 101)
GROUP BY r.Field1

Upvotes: 0

Views: 204

Answers (1)

MikeSmithDev
MikeSmithDev

Reputation: 15797

That's how joins work. You get the total number of results as a result of the joins. So even if the original table only has one row that matches your criteria, the COUNT from a JOIN could have hundreds of results due to one-to-many relationship. You can see why by changing your query:

SELECT  *
FROM    Location1.dbo.Table1 r ( NOLOCK )
        JOIN Location2.dbo.Table2 i ( NOLOCK ) ON r.Field1 = i.Field1
        JOIN Location3.dbo.Table3 rt ( NOLOCK ) ON rt.Field1 = i.Field1
           AND rt.Field2 = r.Field2
WHERE   r.Field3 = '40'
        AND r.Field4 = 'H'
        AND r.Field1 = '516'
        AND CONVERT(CHAR(10), r.TIMESTAMP, 101) = CONVERT(CHAR(10), GETDATE(), 101)

This will return all rows from all tables and you'll see the 134 rows. If you aren't interested in the total, then don't do the join -- since you say that the query without the joins gives you the expected result of 3.

Upvotes: 2

Related Questions