Stored Procedure is bringing back the correct fields but I really need more data

Ok I'm trying to write a SP that when the user puts his or her CustomerOrderID number in and execute, it will bring back all shipping id's and all shipping method descriptions, all flat rates prices and all total shipping weight cost.

So far I have been only able to bring back the shipping method that the customer order table had defined but I really want to give the customer all options available. What should I do I'm stuck on this simply because the shipping method is already defined in another table?


CREATE PROCEDURE [db_owner].[spSelectShippingMethod] @CustomerOrderID int

AS

SELECT  dbo.lu_ShipMethod.ShipMethodID AS [Ship Method ID], dbo.lu_ShipMethod.Description AS [Ship Method Description], 
                      dbo.lu_ShipMethod.FlatRate AS [Flat Rate Charge for Order], dbo.Product.ShippingWeight * dbo.lu_ShipMethod.CostPerPound AS [Cost to ship by pound], 
                    dbo.CustomerOrderItem.CustomerOrderID, dbo.Product.ProductName
FROM         dbo.CustomerOrder INNER JOIN
                      dbo.CustomerOrderItem ON dbo.CustomerOrder.CustomerOrderID = dbo.CustomerOrderItem.CustomerOrderID INNER JOIN
                      dbo.lu_ShipMethod ON dbo.CustomerOrder.ShipMethodID = dbo.lu_ShipMethod.ShipMethodID INNER JOIN
                      dbo.Product ON dbo.CustomerOrderItem.ProductID = dbo.Product.ProductID

WHERE dbo.CustomerOrderItem.CustomerOrderID = @CustomerOrderID

Open in new window

corporateKeenanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
First, for readability sake we need to re-write that query (see below).
Next please post a sample of the output you are getting and the output desired.
CREATE PROCEDURE [db_owner].[spSelectShippingMethod] @CustomerOrderID int

AS 

SELECT  s.ShipMethodID [Ship Method ID],
        s.[Description] [Ship Method Description],
        s.FlatRate [Flat Rate Charge for Order],
        p.ShippingWeight * s.CostPerPound [Cost to ship by pound],
        i.CustomerOrderID,
        p.ProductName
FROM    dbo.CustomerOrder o
        INNER JOIN dbo.CustomerOrderItem i ON o.CustomerOrderID = i.CustomerOrderID
        INNER JOIN dbo.lu_ShipMethod s ON o.ShipMethodID = s.ShipMethodID
        INNER JOIN dbo.Product p ON i.ProductID = p.ProductID
WHERE   i.CustomerOrderID = @CustomerOrderID

Open in new window

0
corporateKeenanAuthor Commented:
Output that I am getting:

ShipID  ShipDescription  FlatRate      CostbyPound  CustomerOrderID    ProductName
3      Next Day Air      25.00      10.72      16      DVD-R
3      Next Day Air      25.00      29.48      16      Phone System Deluxe
3      Next Day Air      25.00      3985.16      16      Boulder - Large

Output that I am wanting:

4      Next Day Air      25.00      10.72      16      DVD-R
3      Ground      9.00      7.75      16      DVD-R
2      FedEx      9.00      8.50      16      DVD-R
1      Freight      70.00      11.45      16      DVD-R

0
SharathData EngineerCommented:
can you try this
CREATE PROCEDURE [db_owner].[SPSELECTSHIPPINGMETHOD] 
                @CustomerOrderID INT 
AS 
  SELECT dbo.lu_ShipMethod.ShipMethodID                              AS [Ship Method ID], 
         dbo.lu_ShipMethod.DESCRIPTION                               AS [Ship Method Description], 
         dbo.lu_ShipMethod.FlatRate                                  AS [Flat Rate Charge for Order], 
         dbo.Product.ShippingWeight * dbo.lu_ShipMethod.CostPerPound AS [Cost to ship by pound], 
         dbo.CustomerOrderItem.CustomerOrderID, 
         dbo.Product.ProductName 
  FROM   dbo.CustomerOrder 
         INNER JOIN dbo.CustomerOrderItem 
           ON dbo.CustomerOrder.CustomerOrderID = dbo.CustomerOrderItem.CustomerOrderID 
         INNER JOIN dbo.Product 
           ON dbo.CustomerOrderItem.ProductID = dbo.Product.ProductID 
         LEFT JOIN dbo.lu_ShipMethod 
           ON dbo.CustomerOrder.ShipMethodID = dbo.lu_ShipMethod.ShipMethodID 
  WHERE  dbo.CustomerOrderItem.CustomerOrderID = @CustomerOrderID

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Anthony PerkinsCommented:
I think I understand, but just to clarify what is the ShipID for "Next Day Air" 3 or 4? The output for your query returns 3, but the desired output has 4.
0
corporateKeenanAuthor Commented:
Unfortunately it is still giving me the same solution.
0
corporateKeenanAuthor Commented:
@aceperkins The "Next Day Air" is 3, I did the wanted output quick handedly so I just through in some numbers per example.
0
corporateKeenanAuthor Commented:
Too sum my issue up in a few words. I want my output to display the all shipping options and what their cost would be for all items ordered by that "CustomerOrderID" number.
0
Anthony PerkinsCommented:
Try it this way:
CREATE PROCEDURE [db_owner].[spSelectShippingMethod] @CustomerOrderID int

AS 

SELECT  s.ShipMethodID [Ship Method ID],
        s.[Description] [Ship Method Description],
        s.FlatRate [Flat Rate Charge for Order],
        p.ShippingWeight * s.CostPerPound [Cost to ship by pound],
        i.CustomerOrderID,
        p.ProductName
FROM    dbo.CustomerOrder o
        INNER JOIN dbo.CustomerOrderItem i ON o.CustomerOrderID = i.CustomerOrderID
        INNER JOIN dbo.Product p ON i.ProductID = p.ProductID
        CROSS JOIN dbo.lu_ShipMethod s
WHERE   i.CustomerOrderID = @CustomerOrderID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
corporateKeenanAuthor Commented:
This did the trick, thank you very much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.