-5

I have these four tables:

  • tblProducts: ProductId, Name, Price, CategoryId
  • tblCategories: CategoryId, Name
  • tblOrders: OrderId, Date, Total
  • tblOrderDetails: OrderDetailsId, OrderId, ProductId, Qty, Price

I want to create a procedure OrderDetails_SelectByOrderId.

I want return type to be complex type in my app and I want these columns : 

ProductId | Qty | Price | CategoryNAME |

I tried everything and cannot get category name in result.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Mozzart
  • 7
  • 2
  • 3
    We need to know what your keys are, how these table are related, what the datatypes are, what the expected output is from a given sample dataset.This seems to be a simple join, regardless of how you slice it though. – S3S Jul 30 '19 at 19:56
  • Bro..keys are int types and...you can see every FKey...i described that in my post... – Mozzart Jul 30 '19 at 19:58
  • All the relations are one to many...this is a most simplest database – Mozzart Jul 30 '19 at 19:59
  • In Products table you have FKey that is CategoryId..and in the OrderDetails table ProductId is a FKey...and OrderId also – Mozzart Jul 30 '19 at 20:00
  • 2
    Nothing in your question identifies which columns are primary or foreign keys or shows their datatypes. Adding in your `create table` DDL script with all constraints would help your question out a lot. – S3S Jul 30 '19 at 20:03
  • Bro..i cannot believe you do not see that...in the products table productId is a PK...in the categories table categoryId is a PK...in the orders table orderId is a PK...in the order details table orderDetailsId is a PK – Mozzart Jul 30 '19 at 20:08
  • In the product table categoryId is a FK...in the order details table productId is a FK and orderId is a FK...all int types...one to many – Mozzart Jul 30 '19 at 20:10
  • 1
    This is a reasonable assumption we could make, but you are missing the point. I think you would benefit on this site by reading [Jon Skeet's](https://stackoverflow.com/users/22656/jon-skeet) blog on [how to ask a question](https://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/). He was the first person to a million reputation so he knows what he is doing.If you want a shorter version, [Spaghettidba](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) is a great choice. **Do your part...** – S3S Jul 30 '19 at 20:11
  • https://stackoverflow.com/a/38578/6167855 – S3S Jul 30 '19 at 20:22

1 Answers1

2

This should do it. If CategoryNAME coming back as null, then you're doing a LEFT OUTER join and have referential integrity issues. 

CREATE PROCEDURE usp_OrderDetails_SelectByOrderId
@OrderID INT
AS
BEGIN
    SELECT P.ProductId,
           OD.Qty,
           P.Price,
           C.NAME AS CategoryNAME
    FROM tblProducts P
    INNER JOIN tblCategories C ON P.CategoryId = C.CategoryId
    INNER JOIN tblOrderDetails OD ON P.ProductId = OD.ProductId
    INNER JOIN tblOrders O ON OD.OrderId = O.OrderId
    WHERE O.OrderId = @OrderId
END
BeardOfTriumph
  • 472
  • 2
  • 8
  • Just a heads up... The OrderID in your WHERE clause is ambiguous. And CategoryNAME isn’t a column. It’s just Name. So, this would error out. – S3S Jul 31 '19 at 00:35
  • 1
    Doh! Thanks. I just copied the columns from his output list. I've fixed my answer. – BeardOfTriumph Jul 31 '19 at 01:02
  • Thanks bro...that is it. No null are alowed on vategoryName or categoryId in the products table. Thanks. – Mozzart Jul 31 '19 at 06:58