count query

[inou]

Could some one help me with a query to do the below.I have 2 tablestblCustomer:ID1 ID2 Fname LnameA1 1 Simth M.A1 2 Bob B.A2 1 David B.A3 2 Derek B.A4 3 Tammy N.A4 1 Ann J. tblRequest:ReID CustID1 CustID21 A1 12 A1 13 A2 14 A2 15 A3 26 A1 17 A2 18 A4 39 A1 210 A4 1 How can I get the result below:CustID1 CustID2 Fname Lname TotalRequestsA1 1 Smith M. 3A1 2 Bob B. 1A2 1 David B. 3A3 2 Derek B. 1A4 3 Tammy N. 1A4 1 Ann J. 1I have tried:SELECTA.[Fname],A.[Lname],A.[iD1],A.[iD2],COUNT(B.[CustID1]) AS TotalRequestFROM[tblCustomer] AS AINNER JOIN[tblRequest] AS BON B.[CustID2] = A.[iD2]WHERE A.[iD1] = B.[CustID1]GROUP BYA.[Fname],A.[Lname],A.[iD1],A.[iD2]ORDER BYA.[iD2],A.[iD1] But not getting the right results.Please help!Thanks in advanced.

Edited June 19, 2012 by inou

[justsomeguy]

It looks fine at first glance, although it would be more efficient to combine the join conditions instead of putting one of them in the where clause. How is the result incorrect?

[inou]

My request tbl contents 900K records but it returns only 400K in total. Something is missing.

[justsomeguy]

You're grouping the requests, it's not going to return 1 row for every request. If you think there are requests that it is not counting, an inner join will leave out records that don't have a match in both tables. So that would mean that the customer that the request is for got deleted.