inou Posted June 18, 2012 Share Posted June 18, 2012 (edited) 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted June 18, 2012 Share Posted June 18, 2012 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? Link to comment Share on other sites More sharing options...
inou Posted June 19, 2012 Author Share Posted June 19, 2012 My request tbl contents 900K records but it returns only 400K in total. Something is missing. Link to comment Share on other sites More sharing options...
justsomeguy Posted June 19, 2012 Share Posted June 19, 2012 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now