Subqueries and Outer Joins

  • Thread starter Thread starter Kirk P.
  • Start date Start date
K

Kirk P.

I've got this SQL giving me a "Syntax error in Join Operation."

SELECT po.merchant_order_id AS order_id, po.source_currency_code AS curr,
po.pymt AS po_pymt, pt.pymt AS pt_pymt
FROM
(SELECT MERCHANT_ORDER_ID, SOURCE_CURRENCY_CODE,
Sum([ORDER_TOTAL]+[COUNTRY_TAX_TOTAL]) AS PYMT FROM tblRC_PAYMENT_ORDERS
GROUP BY MERCHANT_ORDER_ID, SOURCE_CURRENCY_CODE) AS po,
(SELECT ORDER_ID, TRANSACTION_CURRENCY, Sum(PAYMENT) AS PYMT FROM
tblRCN_PAYMENT_TRANSACTION GROUP BY ORDER_ID, TRANSACTION_CURRENCY) AS pt
RIGHT JOIN pt ON po.MERCHANT_ORDER_ID = pt.ORDER_ID;

I can't figure out where the syntax error is - how do I get this query to
work?
 
First, no square brackets allowed in a subquery in a FROM clause.

Second the JOIN should be between the two subqueries and the ON clause
after the two subqueries.

SELECT po.merchant_order_id AS order_id
, po.source_currency_code AS curr
, po.pymt AS po_pymt
, pt.pymt AS pt_pymt
FROM

(SELECT MERCHANT_ORDER_ID
, SOURCE_CURRENCY_CODE,
Sum(ORDER_TOTAL+COUNTRY_TAX_TOTAL) AS PYMT
FROM tblRC_PAYMENT_ORDERS
GROUP BY MERCHANT_ORDER_ID, SOURCE_CURRENCY_CODE) AS po

RIGHT JOIN

(SELECT ORDER_ID, TRANSACTION_CURRENCY, Sum(PAYMENT) AS PYMT
FROM tblRCN_PAYMENT_TRANSACTION GROUP BY ORDER_ID
, TRANSACTION_CURRENCY) AS pt

ON po.MERCHANT_ORDER_ID = pt.ORDER_ID;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Perfect - thank you John!

John Spencer said:
First, no square brackets allowed in a subquery in a FROM clause.

Second the JOIN should be between the two subqueries and the ON clause
after the two subqueries.

SELECT po.merchant_order_id AS order_id
, po.source_currency_code AS curr
, po.pymt AS po_pymt
, pt.pymt AS pt_pymt
FROM

(SELECT MERCHANT_ORDER_ID
, SOURCE_CURRENCY_CODE,
Sum(ORDER_TOTAL+COUNTRY_TAX_TOTAL) AS PYMT
FROM tblRC_PAYMENT_ORDERS
GROUP BY MERCHANT_ORDER_ID, SOURCE_CURRENCY_CODE) AS po

RIGHT JOIN

(SELECT ORDER_ID, TRANSACTION_CURRENCY, Sum(PAYMENT) AS PYMT
FROM tblRCN_PAYMENT_TRANSACTION GROUP BY ORDER_ID
, TRANSACTION_CURRENCY) AS pt

ON po.MERCHANT_ORDER_ID = pt.ORDER_ID;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I've got this SQL giving me a "Syntax error in Join Operation."

SELECT po.merchant_order_id AS order_id, po.source_currency_code AS curr,
po.pymt AS po_pymt, pt.pymt AS pt_pymt
FROM
(SELECT MERCHANT_ORDER_ID, SOURCE_CURRENCY_CODE,
Sum([ORDER_TOTAL]+[COUNTRY_TAX_TOTAL]) AS PYMT FROM tblRC_PAYMENT_ORDERS
GROUP BY MERCHANT_ORDER_ID, SOURCE_CURRENCY_CODE) AS po,
(SELECT ORDER_ID, TRANSACTION_CURRENCY, Sum(PAYMENT) AS PYMT FROM
tblRCN_PAYMENT_TRANSACTION GROUP BY ORDER_ID, TRANSACTION_CURRENCY) AS pt
RIGHT JOIN pt ON po.MERCHANT_ORDER_ID = pt.ORDER_ID;

I can't figure out where the syntax error is - how do I get this query to
work?
Click to expand...
Click to expand...
 
Back
Top