none
SQL Issue RRS feed

  • Question

  • I am trying to link an Orders Table with both a Payment Table and a Customer Table. There may or may not be payments and I need the Orders with no payments to be displayed. I was using the following SQL, but I get a syntax error. Could someone please give me some advice. It would be greatly appreciated.

    SELECT Order.Order_ID, Order.Order_Date, Order.[Order Total], Payment.Payment_ID, Payment.Payment_Date, Payment.Payment_Amount, Customer.Cust_ID, Customer.Cust_LName, Customer.Cust_FName

    FROM ([Order] LEFT JOIN Payment ON Order.[Order_ID] = Payment.[Order_ID]) INNER JOIN (Customer INNER JOIN Order ON Customer.[Cust_ID] = Order.[Cust_ID]) ON Customer.[Cust_ID]=Order.[Cust_ID];

    I have now tried this: 

    SELECT Order.Order_ID, Order.Order_Date, Order.[Order Total], Payment.Payment_ID, Payment.Payment_Date, Payment.Payment_Amount, Customer.Cust_ID, Customer_LName, Customer_FName


    FROM (Order LEFT JOIN Payment on Order.Order_ID = Payment.Order_ID) INNER JOIN Customer ON Payment.Cust_ID = Customer.Cust_ID

    ORDER BY Order.Order_ID, Order.Order_Date, Order.[Order Total], Payment.Payment_ID, Payment.Payment_Date, Payment.Payment_Amount, Customer.Cust_ID, Customer_LName, Customer_FName;



    My latest attempt: 

    SELECT Order.Order_ID, Order.Order_Date, Order.[Order Total], Payment.Payment_ID, Payment.Payment_Date, Payment.Payment_Amount, Customer.Cust_ID

    FROM ([Order] LEFT JOIN Payment ON Order.[Order_ID] = Payment.[Order_ID])
    INNER JOIN Customer on Payment.Cust_ID = Customer.Cust_ID;

    • Edited by stevebmbsqd Tuesday, April 18, 2017 7:15 PM
    Tuesday, April 18, 2017 6:49 PM

All replies

  • Try using all outer joins that go in the same direction outwards from Order. This is easiest to do in Design View.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, April 18, 2017 7:55 PM
  • LEFT JOIN the result set of and INNER JOIN of customers and orders to payments by parenthesizing the INNER JOIN.  The following is an example using the tables from my InvoicePDF demo file
    in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    SELECT Customer,Invoices.InvoiceNumber,InvoiceDate,PaymentDate,Amount
    FROM (Customers INNER JOIN Invoices ON Customers.CustomerID = Invoices.CustomerID)
    LEFT JOIN Payments ON Invoices.InvoiceNumber = Payments.InvoiceNumber
    ORDER BY Customer,Invoices.InvoiceNumber,InvoiceDate,PaymentDate;

    Ken Sheridan, Stafford, England


    Tuesday, April 18, 2017 9:03 PM