January 20, 2011 at 10:39 am
The code you gave me errors
what if i add
What about this?
Inner Join Products
ON Products.Productname = OrderDetails.ProductName,
Products.ProductCode = OrderDetails.ProductCode
Would that be better
January 20, 2011 at 10:40 am
Please, please look at the code and explanation I've posted for you twice
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:46 am
Select
Orders.OrderDate,
Orders.OrderId,
Orders.OrderNotes,
Orders.Custom_field_vehicleinfo,
Orders.Salesrep_Customerid,
OrderDetails.ProductCode,
OrderDetails.Productname,
OrderDetails.Quantity,
Products.CustomField4
From Orders
INNER join Orderdetails
on orders.orderid = orderdetails.orderid
Where Orders.Orderstatus in ('Pending', 'Processing', 'New')
Join Products
ON Products.ProductCode = OrderDetails.ProductCode
Is the code i am using and it is saying incorrect syntax near the word JOIN do i need to have a statement connecting or linking the two joins together?
January 20, 2011 at 10:50 am
The format of a SELECT is...
SELECT <column list>
FROM <table list WITH joins>
WHERE <conditions>
GROUP BY <column list>
HAVING <Conditions>
ORDER BY <Columns>
All but the SELECT are optional. The order cannot be changed and cannot be mixed up.
Now, as I've said already, you probably want this form.
FROM Orders
INNER JOIN OrderDetails ON ...
INNER JOIN Products ON ...
WHERE <conditions>
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:53 am
So how would i have my where clause in there in the correct order?
January 20, 2011 at 10:55 am
When i switch it to this so the WHERE clause is at the end
SELECT
Orders.OrderDate,
Orders.OrderId,
Orders.OrderNotes,
Orders.Custom_field_vehicleinfo,
Orders.Salesrep_Customerid,
OrderDetails.ProductCode,
OrderDetails.Productname,
OrderDetails.Quantity,
Products.CustomField4
FROM Orders
INNER JOIN Products
ON Products.ProductCode = OrderDetails.ProductCode
INNER JOIN Orderdetails
ON orders.orderid = orderdetails.orderid
WHERE Orders.Orderstatus in ('Pending', 'Processing', 'New')
I get this ERROR
Your SQL is invalid: The multi-part identifier "OrderDetails.ProductCode" could not be bound.
January 20, 2011 at 10:59 am
SELECT
Orders.OrderDate,
Orders.OrderId,
Orders.OrderNotes,
Orders.Custom_field_vehicleinfo,
Orders.Salesrep_Customerid,
OrderDetails.ProductCode,
OrderDetails.Productname,
OrderDetails.Quantity,
FROM Orders
INNER JOIN Orderdetails
ON orders.orderid = orderdetails.orderid
WHERE Orders.Orderstatus in ('Pending', 'Processing', 'New')
ORDER BY Orders.OrderDate ASC
I really jus need to have CustomField4 from products so up on this report.... CustomField4 is linked to the orderdetails table threw product name and productcode.
That is my problem
January 20, 2011 at 11:04 am
Are you reading my posts?
You need this form of query. All you have to do is fill in the join conditions, fill in the where clause and the order by and put the select.
FROM Orders
INNER JOIN OrderDetails ON ...
INNER JOIN Products ON ...
WHERE <conditions>
ORDER BY <columns>
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 11:15 am
So you are saying to look like this
SELECT
Orders.OrderDate,
Orders.OrderId,
Orders.OrderNotes,
Orders.Custom_field_vehicleinfo,
Orders.Salesrep_Customerid,
OrderDetails.ProductCode,
OrderDetails.Productname,
OrderDetails.Quantity,
Products.CustomFeild4
FROM Orders
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
INNER JOIN Products ON Products.ProductCode = OrderDtails.ProductCode
WHERE Orders.Orderstatus in ('Pending', 'Processing', 'New')
ORDER BY Orders.OrderDate ASC
January 20, 2011 at 11:17 am
Justin 17041 (1/20/2011)
So you are saying to look like this
SELECT
Orders.OrderDate,
Orders.OrderId,
Orders.OrderNotes,
Orders.Custom_field_vehicleinfo,
Orders.Salesrep_Customerid,
OrderDetails.ProductCode,
OrderDetails.Productname,
OrderDetails.Quantity,
Products.CustomFeild4
FROM Orders
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
INNER JOIN Products ON Products.ProductCode = OrderDtails.ProductCode
WHERE Orders.Orderstatus in ('Pending', 'Processing', 'New')
ORDER BY Orders.OrderDate ASC
When i run this is get
Your SQL is invalid: The multi-part identifier "OrderDtails.ProductCode" could not be bound.
January 20, 2011 at 11:19 am
Precisely.
When you build up the from clause, you can only use columns from tables that you've already specified, so make sure you get the order of the tables correct. In this case you need orders first, as it's the main table. There's no reference from orders to products, so you can't add products as the second table, you need to add order details. Then, with both orders and order details defined, you have the columns necessary to join in products.
Think of it as a multi-story building. You can't build the third floor before you've built the second floor.
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 11:20 am
Justin 17041 (1/20/2011)
Your SQL is invalid: The multi-part identifier "OrderDtails.ProductCode" could not be bound.
That's because you misspelt it. The table is not OrderDtails, it's OrderDetails
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 11:30 am
Ok not it all works but it is saying
"Your SQL is invalid: Invalid column name 'customfield4'."
Do i need to have quotes around it or anything sense there is a number in it?
January 20, 2011 at 11:32 am
Again I think you misspelt it. You have CustomFeild4 in your query, I doubt it's misspelt in the table.
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 11:34 am
GilaMonster (1/20/2011)
Again I think you misspelt it. You have CustomFeild4 in your query, I doubt it's misspelt in the table.
its spelled correct i copy and pasted it directly from my excel database export file
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply