Nate_T Nate_T - 5 months ago 11
SQL Question

Incorrect syntax near '.' in SQL

I'm trying to do a SQL query with creating a view on a database that I've made earlier. On viewing tables with joins earlier it was working fine, however now when I'm trying to create a view I get a "Incorrect syntax near '.'" error message from the first INNER JOIN statement. Why would this occur?

CREATE VIEW vm_order_cost
AS
SELECT orders.order_id,
orders.order_date,
products.product_id,
customers.name,
order_cost = (order_details.quantity * products.unit_price)
FROM orders
INNER JOIN customers AS orders.customer_id = customers.customer_id
INNER JOIN order_details AS orders.order_id = order_details.order_id
INNER JOIN products AS order_details.product_id = products.product_id
WHERE order_id BETWEEN '10000' AND '10200'
GO

Answer

You should be using ON instead of AS:

CREATE VIEW vm_order_cost
AS
SELECT  orders.order_id,
    orders.order_date,
    products.product_id,
    customers.name,
    order_cost = (order_details.quantity * products.unit_price)
FROM orders
    INNER JOIN customers ON orders.customer_id = customers.customer_id
    INNER JOIN order_details ON orders.order_id = order_details.order_id
    INNER JOIN products ON order_details.product_id = products.product_id
WHERE order_id BETWEEN '10000' AND '10200'
Comments