SQL Query: joining three tables to generate a Sales Report of Spend

I normally work with MySQL so not sure how to get a query to work on SQL.

I have 3 tables:

Table1 ODBC_ORDER_LINE_ALL

This contains ORDER_LINE_NETT_TOTAL and also the ORDER_LINE_ORDER_ID

Table 2 ODBC_ORDER_ALL

This contains the ORDER_ID and also the ORDER_ACCOUNT_ID

Table 3 ODBC_ACCOUNT

This contains the ACCOUNT_ACCID and some other information on the account we may need in the future, hence why we are adding it.

I am joining table 1,2,3 together using the above constraints.

What I want to be able to do is generate a Sales Report of Spend showing Account Number and 开发者_StackOverflow中文版Total Spend but I can't seem to get the syntax correct.

Any help much appreciated.

Here are the tables:

ODBC_ORDER_LINE_ALL

ORDER_LINE_ID

ORDER_LINE_ORDER_ID

ORDER_LINE_DATE

ORDER_LINE_MEDIACODE

ORDER_LINE_SOURCE

ORDER_LINE_PRODUCT_ID

ORDER_LINE_PRODUCT_CODE

ORDER_LINE_PRODUCT_NAME

ORDER_LINE_QUANTITY

ORDER_LINE_NETT_PRICE

ORDER_LINE_VAT_CODE

ORDER_LINE_VAT_RATE

ORDER_LINE_VAT

ORDER_LINE_NETT_TOTAL

ORDER_LINE_VAT_TOTAL

ORDER_LINE_GROSS_TOTAL

ORDER_LINE_UNIT_COST

ORDER_LINE_COST_CURRENCY

ORDER_LINE_COST_EXCHANGE_RATE

ORDER_LINE_TYPE

ORDER_LINE_STAGE

ORDER_LINE_STAGE_DATE

ORDER_LINE_INVOICE_ID

ORDER_LINE_INVOICE_DATE

ORDER_LINE_KIT_COMPONENT

ORDER_LINE_KIT_LINE_ID

ORDER_LINE_USER

ODBC_ORDER_ALL

ORDER_ID

ORDER_TYPE

ORDER_MEDIA_CODE_ID

ORDER_MEDIA_CODE

ORDER_MEDIA_SUBCODE

ORDER_TAKEN_BY

ORDER_DATE

ORDER_ACCOUNT_ID

ORDER_DELIVERY_ACCOUNT_ID

ORDER_SOURCE_ID

ORDER_SOURCE

ORDER_WEB_ORDER_ID

ORDER_MULTI_CLIENT_ID

ORDER_MULTI_CLIENT

ODBC_ACCOUNT

ACCOUNT_ACCID

ACCOUNT_ACC_TYPE

ACCOUNT_REF

ACCOUNT_TITLE

ACCOUNT_FORENAME

ACCOUNT_SURNAME

ACCOUNT_COMPANY_NAME

ACCOUNT_HOUSE_NAME

ACCOUNT_ADD1

ACCOUNT_ADD2

ACCOUNT_ADD3

ACCOUNT_ADD4

ACCOUNT_POSTCODE

ACCOUNT_COUNTRY

ACCOUNT_PHONE1

ACCOUNT_PHONE2

ACCOUNT_FAX1

ACCOUNT_FAX2

ACCOUNT_EMAIL

ACCOUNT_WEBSITE

ACCOUNT_PRICELIST_ID

ACCOUNT_MEDIACAMPAIGN_ID

ACCOUNT_CREATED_DATE

---

Assuming that the table names are 'Table 1' ODBC_Order_Line_All, 'Table 2' ODBC_Order_All, 'Table 3' ODBC_Account, then you simply need to join the three tables on the relevant joining columns, and sum the order line nett total entries for each account:

SELECT A.Account_AccID, SUM(L.Order_Line_Nett_Total) AS TotalSpend
  FROM ODBC_Account        AS A
  JOIN ODBC_Order_All      AS O ON A.Account_Acc_ID = O.Order_Account_ID
  JOIN ODBC_Order_Line_All AS L ON O.Order_ID = L.Order_Line_Order_ID
 GROUP BY A.Account_AccID;

However, while there is nothing but the Account_AccID selected from ODBC_Account, there is no actual need to select from the ODBC_Account table; you can join just the two tables, yielding a simpler query:

SELECT O.Order_Account_ID  AS Acount_AccID, SUM(L.Order_Line_Nett_Total) AS TotalSpend
  FROM ODBC_Order_All      AS O ON A.Account_Acc_ID = O.Order_Account_ID
  JOIN ODBC_Order_Line_All AS L ON O.Order_ID = L.Order_Line_Order_ID
 GROUP BY Account_AccID;

There is nothing here that is different in MySQL from any other SQL DBMS, so your knowledge of MySQL should transfer directly.