January 20, 2011 at 10:05 am
Select
Orders.OrderDate,
Orders.OrderId,
Orders.OrderNotes,
Orders.Custom_field_vehicleinfo,
Orders.Salesrep_Customerid,
OrderDetails.ProductCode,
OrderDetails.Productname,
OrderDetails.Quantity,
Products.CustomFeild4
From Orders
Join Products
ON Orderdetails.ProductName = Products.ProductName
Order BY Products.ProductCode ASC
join Orderdetails
on orders.orderid = orderdetails.orderid
Where Orders.Orderstatus in ('Pending', 'Processing', 'New')
Order By Orders.Orderdate ASC
Error incorrect syntax near the work join......
January 20, 2011 at 10:12 am
ORDER BY must be present at the last of the code. That is where it should be; so your new code looks like
Select
Orders.OrderDate,
Orders.OrderId,
Orders.OrderNotes,
Orders.Custom_field_vehicleinfo,
Orders.Salesrep_Customerid,
OrderDetails.ProductCode,
OrderDetails.Productname,
OrderDetails.Quantity,
Products.CustomFeild4
From Orders
Join Products
ON Orderdetails.ProductName = Products.ProductName
/* Order BY Products.ProductCode ASC */ -- commenting this order by
join Orderdetails
on orders.orderid = orderdetails.orderid
Where Orders.Orderstatus in ('Pending', 'Processing', 'New')
Order By Products.ProductCode ASC ,
Orders.Orderdate ASC
January 20, 2011 at 10:14 am
So take out the first order by and add it at the end before date?
January 20, 2011 at 10:15 am
Now i get
Your SQL is invalid: The multi-part identifier "Orderdetails.ProductName" could not be bound.
January 20, 2011 at 10:16 am
is there a way to join the products without using "on"
January 20, 2011 at 10:22 am
No. A join needs an ON to say which columns are matched in the join.
I suspect that the tables are just referenced in the wrong order in the FROM clause. You can't mention a table in a join before mentioning the table itself.
FROM Orders
INNER JOIN OrderDetails ON ...
INNER JOIN Products ON ...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2011 at 10:27 am
Select
Orders.OrderDate,
Orders.OrderId,
Orders.OrderNotes,
Orders.Custom_field_vehicleinfo,
Orders.Salesrep_Customerid,
OrderDetails.ProductCode,
OrderDetails.Productname,
OrderDetails.Quantity,
Products.CustomFeild4
From Orders
Join Products
ON Orderdetails.ProductName = Products.ProductName
Order BY Products.ProductCode ASC
join Orderdetails
on orders.orderid = orderdetails.orderid
Where Orders.Orderstatus in ('Pending', 'Processing', 'New')
Order By Orders.Orderdate ASC
Error incorrect syntax near the work join.....
Is the exact code i am running and the error i get
In the products table it has the column names ProductCode as well as ProductName that are the same in table order details. i need to get the CustomField4 from products to appear on the report as well.
January 20, 2011 at 10:28 am
Well the the columns match on ProductName and ProductCode so what should i do?
January 20, 2011 at 10:30 am
You likely need something like this:
From Orders o
Inner Join OrderDetails od
ON o.orderid = od.orderid
Inner Join Products p
on o.productName = p.productname
January 20, 2011 at 10:31 am
Justin, the code that i had given, did u run it without editing anything ? whats the result of that?
January 20, 2011 at 10:31 am
What about this?
Inner Join Products
ON Products.Productname = OrderDetails.ProductName,
Products.ProductCode = OrderDetails.ProductCode
January 20, 2011 at 10:36 am
GilaMonster (1/20/2011)
I suspect that the tables are just referenced in the wrong order in the FROM clause. You can't mention a table in a join before mentioning the table itself.
FROM OrdersINNER JOIN OrderDetails ON ...
INNER JOIN Products ON ...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2011 at 10:37 am
Justin 17041 (1/20/2011)
What about this?
Inner Join ProductsON Products.Productname = OrderDetails.ProductName ,
Products.ProductCode = OrderDetails.ProductCode
Remove the , and replace it with AND like this
Inner Join Products
ON Products.Productname = OrderDetails.ProductName and
Products.ProductCode = OrderDetails.ProductCode
January 20, 2011 at 10:37 am
Why is the product name in the order details? It shouldn't be there, it should only be in the products table. The product code only should be in the order details table.
The way you have it if you change the name of a product you have to change it in multiple places.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2011 at 10:39 am
ColdCoffee (1/20/2011)
Remove the , and replace it with AND like this
Inner Join Products
ON Products.Productname = OrderDetails.ProductName and
Products.ProductCode = OrderDetails.ProductCode
Except that there's no reason to join on both unless it's possible to have two products with the same code and different names.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply