SQL Server Performance

A small Doubt

Discussion in 'SQL Server 2008 General Developer Questions' started by alisag, Jan 28, 2010.

  1. alisag New Member

    Hi All,
    I wrote one query as
    SELECT ProdOrder.No_, ProdOrder.Description, ProdOrder.Status, ProdOrder.Quantity,ProdOrder.[Location Code],ProdOrder.[Source No_],Table1.[Unit Cost],Table2.[Quantity on Hand]
    LEFT OUTER JOIN
    (SELECT No_, [Customer No_],[Unit Cost]
    FROM [Company Name$Item]
    AS Table1 ON ProdOrder.[Source No_] = Table1.No_
    LEFT OUTER JOIN

    (SELECT [Item No_], ISNULL(SUM(Quantity), 0) AS [Quantity on Hand]
    FROM [Company Name$Item Ledger Entry]
    GROUP BY [Item No_]) AS Table2 ON ProdOrder.[Source No_] = Table2.[Item No_]


    WHERE (ProdOrder.Status = 3)
    The PK of Prod Table is (Status, No_). Now, i have one more table called customer which is related to Item Table by Item.Customer = Customer .[No_]. I want to get the name of customer. The problem is that ProdOrder Table is my main table but there is no link with customer Table
    ProdOrder Table is linked to Item as shown in the query.
  2. preethi Member

    First of all I don't see the FROM clause in your query. Is there a missing part?
    If you want to get the customer name, you need to join customer table. As you mentioned you need to have an INNER JOIN with Item in the way you have specified.As Item is already connected to ProdOrder that join should work, unless you haven;t specified something.
    Please go though Books online for further information.
  3. alisag New Member

    Hi Preethi,
    Thanks.
    I have modified the query. This is wat you were saying?
    SELECT ProdOrder.No_, ProdOrder.Description, ProdOrder.Status, ProdOrder.Quantity,ProdOrder.[Location Code],ProdOrder.[Source No_],ProdOrder.[Location Code],Table1.[Unit Cost],(ProdOrder.Quantity*Table1.[Unit Cost] )AS [Output Cost],Table2.[Expected Operation Cost Amt],Table3.[Expected Component Cost Amt],(Table2.[Expected Operation Cost Amt_]+Table3.[Expected Component Cost Amt_] )AS [Expected Amount],((ProdOrder.Quantity*Table1.[Unit Cost] )-(Table2.[Expected Operation Cost Amt_]+Table3.[Expected Component Cost Amt_] ))AS [Expected Variance],Cust.Name
    From [Company Name$Production Order] AS ProdOrder
    LEFT OUTER JOIN
    (SELECT [No_], [Customer No_],[Unit Cost]
    FROM [Company Name$Item])
    AS Table1 ON ProdOrder.[Source No_] = Table1.No_
    LEFT OUTER JOIN

    (SELECT [Prod. Order No_], Status,SUM(Expected Operation Cost Amt_) AS [Expected Operation Cost Amt]
    FROM [Company Name$Prod_ Order Routing Line]
    GROUP BY [Prod. Order No_],Status) AS Table2 ON ProdOrder.No_ = Table2.[Prod. Order No_] AND Table2.Status = 2
    LEFT OUTER JOIN

    (SELECT [Prod. Order No_], Status,SUM(Cost Amount) AS [Expected Component Cost Amt]
    FROM [Company Name$Prod_ Order Component]
    GROUP BY [Prod. Order No_],Status) AS Table3 ON ProdOrder.No_ = Table3.[Prod. Order No_] AND Table3.Status = 2
    Inner Join
    (Select [No_],Name from [Company Name$Customer]) As Cust On Table1.[Customer No.] = Cust.[No_]

    WHERE (ProdOrder.Status = 2)
  4. preethi Member

    Seems okay; Is it giving the desired results?
  5. alisag New Member

    Yup Now its working fine.
  6. dineshasanka Moderator

Share This Page