sql - Incorrect Count with Multiple Joins -


i'm getting incorrect count when use multiple 'joins'. should show 3 total it's returning 134 total. what's proper way use count multiple 'joins'?

select  r.field1       , total = count(r.field1)    location1.dbo.table1 r ( nolock )         join location2.dbo.table2 ( nolock ) on r.field1 = i.field1         join location3.dbo.table3 rt ( nolock ) on rt.field1 = i.field1                                                    , rt.field2 = r.field2   r.field3 = '40'         , r.field4 = 'h'         , r.field1 = '516'         , convert(char(10), r.timestamp, 101) = convert(char(10), getdate(), 101) group r.field1 

that's how joins work. total number of results result of joins. if original table has 1 row matches criteria, count join have hundreds of results due one-to-many relationship. can see why changing query:

select  *    location1.dbo.table1 r ( nolock )         join location2.dbo.table2 ( nolock ) on r.field1 = i.field1         join location3.dbo.table3 rt ( nolock ) on rt.field1 = i.field1            , rt.field2 = r.field2   r.field3 = '40'         , r.field4 = 'h'         , r.field1 = '516'         , convert(char(10), r.timestamp, 101) = convert(char(10), getdate(), 101) 

this return rows tables , you'll see 134 rows. if aren't interested in total, don't join -- since query without joins gives expected result of 3.


Comments

Popular posts from this blog

php - regexp cyrillic filename not matches -

c# - OpenXML hanging while writing elements -

sql - Select Query has unexpected multiple records (MS Access) -