taking count from 3 table

[Ahusna]

I have three tables, history table, office table and invoice .
I Want to total count of each office who has sent the invoice based on the criteria that matches.

This is my query please help me.

SELECT DISTINCT TOP (100) PERCENT dbo.tbl_jobHistory.RCN, dbo.tbl_invoice.Date, dbo.tbl_office.officeName
FROM         dbo.tbl_jobHistory INNER JOIN
              dbo.tbl_office ON    dbo.tbl_jobHistory.officeID = dbo.tbl_office.officeID LEFT OUTER JOIN
                      dbo.tbl_invoice ON dbo.tbl_jobHistory.invoiceID = dbo.tbl_invoice.invoiceID
WHERE     (dbo.tbl_jobHistory.actionType = 'RDP') AND (NOT (dbo.tbl_invoice.state = 'CA')) AND (dbo.tbl_chits.effectiveDate BETWEEN CONVERT(DATETIME, 
                      '2011-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-12-31 00:00:00', 102)) AND (dbo.tbl_office.officeName = dbo.tbl_office.officeName)
ORDER BY dbo.tbl_office.officeName

[Rabbit]

Please use code tags when posting code.

A few things.

• Why are you using distinct?
• Why are you using top if you're going to return everything anyways?
• Your last where condition doesn't do anything.
• If you want to do a count, why don't I see the count function anywhere?
• Sample data and results are needed because your description is too vague.