Hi all, Can anyone help please I'm happy joining 2 table etc but how do I add in a 3rd-4th table to a query below? I needed was to get the sums and group by of the 3rd and 4th table... The 2 new tables are: ViewDis (X) - Field Sum (Distance) ViewACC (Y) - Fields Sum (Hours, Min, (Group by) Building and Date) Select Distinct P.CustID, P.Acct, P.Title, P.FirstName, P.LastName, A.Trips, A.MoneySpent, A.Bal, X.Distance, Y.Hours, Y.Min, Y.Building FROM dbo.Cust P INNER JOIN (SELECT CustID, Sum(Days) as Trips, SUM(MoneySpent) as MoneySpent, Sum(Balance) as Bal, Sum(Distance) as Dist, Sum(Hours), Sum(Min) FROM dbo.CAsh Where Accumulator='DP' GROUP BY CustID) A ON P.CustID = A.CustID INNER JOIN dbo.viewDis X ON v1.CustID = A.CustID INNER JOIN dbo.viewAcc Y ON v2.CustID = A.CustID Im not sure if the inner join is in the correct place and if I reference the fields correctly. Thanks for you help. Sql Newbie
Without having some data to play with, I'd say that your query is close. In the INNER JOIN's you specified a 'v1' & 'v2', however they don't correlate to any known alias in your query that I can see. My guess is that you would need to change the query to something like this: SELECT DISTINCT P.CustID , P.Acct , P.Title , P.FirstName , P.LastName , A.Trips , A.MoneySpent , A.Bal , X.Distance , Y.Hours , Y.Min , Y.Building FROM dbo.Cust P INNER JOIN ( SELECT CustID , SUM(Days) AS Trips , SUM(MoneySpent) AS MoneySpent , SUM(Balance) AS Bal , SUM(Distance) AS Dist , SUM(Hours) , SUM(Min) FROM dbo.CAsh WHERE Accumulator = 'DP' GROUP BY CustID ) A ON P.CustID = A.CustID INNER JOIN dbo.viewDis X ON X.CustID = A.CustID INNER JOIN dbo.viewAcc Y ON Y.CustID = A.CustID This would join the new tables to correlated query, however depending on your data and what the results need to look like, you might need to adjust this accordingly. Hope this helps!
Hi John, TheorWin is in both tables but for the specific task I need the sum of the X.theorwin. I used the physical name X.theorwin but now faced with -- Msg 8155, Level 16, State 2, Line 2 No column was specified for column 32 of 'A'.