Help with Inner Joins

[MichaelaLee]

Hi Everyone,
I'm having a slight problem with a query I'm inporting from MS Access to SQL Server. One problem is that when I run the query in SQL Query Analyzer I get no results, but is I run it in Access with the tables attached (dbo_ in the query) I get the results I expect. Can you look at the query and se if there is anything unusual about it. Here is the sql:
SELECT Programs.ProgramID, Programs.ProgramName, dbrders.OrderId, dbrders.OrderDate, Products.UnitPrice, dbrderDetail.Quantity, dbrderDetail.ProductId, Products.ProductName
FROM Products INNER JOIN ((Programs INNER JOIN dbrders ON Programs.ProgramID = dbrders.ProgramId) INNER JOIN dbrderDetail ON dbrders.OrderId = dbrderDetail.OrderId) ON Products.ProductID = dbrderDetail.ProductId;

What do you all think. I hope you can help. Thanks
Michael

 

[SQLSister]

dbrders, dbrderDetail are probably the problem. Access adds the owner prefix and an underscore to the table when you link it. The tables are not named this in SQL Server. Try Orders and OrderDetail instead.

Questions about posting. See faq183-874

 

[MichaelaLee]

Hi SQLSister,
Thanks for the reply. Sorry I was not more presice. In the query under SQL Server, I don't use the dbo_ on the table name. I undertand that will not work. Here is the sql in SQL Server:
SELECT Programs.ProgramID, Programs.ProgramName, Orders.OrderID, Orders.OrderDate,
Products.UnitPrice, OrderDetail.Quantity, OrderDetail.ProductID, Products.ProductName
FROM Products INNER JOIN ((Programs INNER JOIN Orders
ON Programs.ProgramID = Orders.ProgramID) INNER JOIN OrderDetail
ON Orders.OrderID = OrderDetail.OrderID)
ON Products.ProductID = OrderDetail.ProductID

I hope this clears the confusion up. THanks again for the reply. What do you think might be the problem.
Michael

 

[SQLSister]

Have you tried it without the parentheses in the join?

Questions about posting. See faq183-874

 

[MichaelaLee]

Hi SQLSister,
I found the problem. It turned out that there was a data problem. Corrected that and things look alright so far. THanks for your help.

Michael

 

[SQLSister]

DArn data problems! Glad you got it fixed.

Questions about posting. See faq183-874