Unwanted duplicate records

Transact-SQL
,
1

Using SQL Server 2008.
The problem is it's bringing back duplicate OrderID.
I've marked the problem joins.
Even tried OUTER APPLY but it caused certain searches not to work.
Tried INNER APPLY then duplicates again.
The problem joins, the tables, have multiple references to OrderID.
So tblRun has multiple rows with the same OrderID showing which run it was in at and what date and so forth.

I really need suggestions from all the guru's out there.

Here is the sql:

    DECLARE @CompanyID        INT                = 22718,
    @StartDate                DATETIME        = '',
    @EndDate                DATETIME        = '',
    @SalesRepID                INT                = NULL,
    @AssignedTo                INT                = NULL,
    @ServiceDefID            INT                = NULL,
    @ProductName            VARCHAR(512)    = NULL,
    @IsCCOrder                BIT                = NULL,
    @OrderID                INT                = NULL,
    @LocationID                INT                = NULL,
    @SalesRepLocationID        INT                = NULL,
    @PONum                    VARCHAR(150)    = NULL,
    @InvoiceID                INT                = NULL,
    @IsSplitOrder            BIT                = NULL,    
    @ContactID                INT                = NULL,
    @ContactName            VARCHAR(150)    = NULL,
    @JobName                VARCHAR(200)    = NULL,
    @Notes                    VARCHAR(MAX)    = NULL,
    @CompanyName            VARCHAR(255)    = NULL,
    @DueDateFrom            DATETIME        = '',
    @DueDateTo                DATETIME        = '',
    @SubmitedDateFrom        DATETIME        = '',
    @SubmitedDateTo            DATETIME        = '',
    @IsRush                    BIT                = NULL,
    @Msg                    VARCHAR(1000)    = NULL
    
    --@Stages               dbo.int_tbltype READONLY
    

DECLARE @Stages TABLE (ID INT)
--INSERT INTO @Stages (ID) VALUES (1)
    

DECLARE @DueDate DATETIME = NULL


SET NOCOUNT ON

DECLARE @OrderIDsTBL TABLE(OrderID INT)
IF @Msg IS NOT NULL
BEGIN
    INSERT INTO @OrderIDsTBL (OrderID)
    SELECT OrderID FROM tblOrderLog
    WHERE Msg LIKE '%' + @Msg + '%'
END

IF @OrderID IS NOT NULL
BEGIN
    INSERT INTO @OrderIDsTBL (OrderID)
    VALUES (@OrderID)
END

DECLARE @OderIDsCnt INT = (SELECT COUNT(OrderID) FROM @OrderIDsTBL)
DECLARE @StageCnt INT = (SELECT COUNT(ID) FROM @Stages)



SELECT 
    o.OrderID,
    o.CompanyID,
    co.Name AS CompanyName,
    o.ContactID,
    o.JobName,
    p.FirstName + ' ' + p.LastName AS ContactName,    
    p2.FirstName + ' ' + p2.LastName AS SalesRep,
    o.DueDate,
    CASE WHEN MAX(oi.PriorityService) > 0 THEN 1 ELSE 0 END AS IsRush,
    ISNULL(s.StageID, 0) AS StageID,
    o.Notes, r.SubmittedComplete,
    dbo.fOrderRunLocationCSVByOrderID(o.OrderID) AS LocationCSV,
    
        (SELECT
        STUFF((SELECT DISTINCT ' ' + st.Name + '<br />' FROM tblStage st
        INNER JOIN tblOrderItem oi ON oi.OrderID = o.OrderID
        INNER JOIN tblRun r ON r.OrderItemID = oi.OrderItemID
        INNER JOIN tblStage s ON s.StageID = r.StageID
        LEFT JOIN tblRunService rs ON rs.RunID = r.RunID
        WHERE (s.StageID = st.StageID)
        AND (rs.AssignedTo = @AssignedTo OR @AssignedTo IS NULL)
        FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')) 
        AS Stages,
    Row_Number() Over(Order By o.OrderID Desc) As RowNum
FROM tblOrder o
    INNER JOIN tblCompany co ON co.CompanyID = o.CompanyID
    INNER JOIN tblParty p ON p.PartyID = o.ContactID

-------- PROBLEM JOINS ------------
        **LEFT JOIN tblOrderItem oi ON oi.OrderID = o.OrderID**
        **LEFT JOIN tblRun r ON r.OrderItemID = oi.OrderItemID**
        **LEFT JOIN tblService srv ON srv.OrderItemID = oi.OrderItemID**
-------- END PROBLEM JOINS ------------
    
    --OUTER APPLY
 --  (
    --    SELECT  TOP (1) OrderItemID, Name, PriorityService, Notes,
    --        SplitOrderID
    --    FROM tblOrderItem oi
    --    WHERE oi.OrderID = o.OrderID
 --  ) oi
 --   OUTER APPLY
 --   (
    --    SELECT TOP (1) SubmittedComplete, StageID
    --    FROM tblRun r
    --    WHERE r.OrderItemID = oi.OrderItemID
    --    ORDER BY SubmittedComplete DESC
    --) r
    --OUTER APPLY
    --(
    --    SELECT TOP (1) ServiceID, ServiceDefID, LocationID
    --    FROM tblService srv
    --    WHERE srv.OrderItemID = oi.OrderItemID
    --    ORDER BY ServiceID DESC
    --) srv
    
    LEFT JOIN tblStage s ON s.StageID = r.StageID    
    LEFT JOIN tblParty p2 ON p2.PartyID = o.SalesRepID
    LEFT JOIN tblEmployee e ON e.EmployeeID = o.SalesRepID
    LEFT JOIN tblShipTo st ON o.ShipToID = st.ShipToID
    WHERE 
    (@CompanyID IS NULL OR ( o.CompanyID = @CompanyID )) AND
    (@IsCCOrder IS NULL OR ( ISNULL(o.IsCreditCardOrder, 0) = @IsCCOrder )) AND
    (@SalesRepID IS NULL OR o.SalesRepID = @SalesRepID ) AND
    (@ServiceDefID IS NULL OR ( srv.ServiceDefID = @ServiceDefID )) AND
    (@ProductName IS NULL OR ( oi.Name LIKE '%' + @ProductName + '%')) AND 
    
    (@IsSplitOrder IS NULL OR ( @IsSplitOrder = 1 AND oi.SplitOrderID IS NOT NULL)) AND
    (
      ( @StartDate = '' OR @EndDate = '' ) OR 
      ( @StartDate >= CreatedDate AND @EndDate <= COALESCE(CancelledDate, ClosedDate, GetDate()) ) OR
      ( @StartDate  <= COALESCE(CancelledDate, ClosedDate, GETDATE()) AND @EndDate >= COALESCE(CancelledDate, ClosedDate, GetDate()) ) OR
      ( @StartDate <= CreatedDate AND @EndDate >= CreatedDate ) 
    ) AND 
    
    (@LocationID IS NULL OR (@LocationID = srv.LocationID OR srv.LocationID IS NULL)) AND
    (@SalesRepLocationID IS NULL OR (@SalesRepLocationID = e.LocationID OR e.LocationID IS NULL))
    AND (@InvoiceID IS NULL OR o.InvoiceID = @InvoiceID ) 
    AND (@PONum IS NULL OR o.PONum LIKE '%' + @PONum + '%')
    AND (COALESCE(s.StageID, 0) IN (SELECT ID FROM @Stages) OR @StageCnt = 0)

    AND (o.ContactID = @ContactID OR @ContactID IS NULL)
    AND (p.FirstName + ' ' + p.LastName LIKE '%' + @ContactName + '%' OR @ContactName IS NULL)
    AND (o.JobName LIKE '%' + @JobName + '%' OR @JobName IS NULL)
    AND (o.Notes LIKE '%' + @Notes + '%' OR @Notes IS NULL)
    AND (co.Name LIKE '%' + @CompanyName + '%' OR @CompanyName IS NULL)
    AND (o.DueDate >= @DueDateFrom OR @DueDateFrom = '')
    AND (o.DueDate <= @DueDateTo OR @DueDateTo = '')
    AND (r.SubmittedComplete >= @SubmitedDateFrom OR @SubmitedDateFrom = '')
    AND (r.SubmittedComplete <= @SubmitedDateTo OR @SubmitedDateTo = '')
    AND (@IsRush = (CASE WHEN oi.PriorityService > 0 THEN 1 ELSE 0 END)
         OR @IsRush IS NULL)
    AND (o.OrderID IN (SELECT OrderID FROM @OrderIDsTBL) OR @OderIDsCnt = 0)
    
GROUP BY 
    o.OrderID,
    o.CompanyID,
    co.Name,
    o.ContactID,
    o.JobName,
    p.FirstName,
    p.LastName,    
    p2.FirstName,
    p2.LastName,
    o.DueDate,
    o.Notes,
    r.SubmittedComplete,
    s.StageID

Thanks for any suggestions. I've been working on this for some time now and just can't get it working right.

2

One of your joins is returning multiple rows for one each row of the row set. To find out with, start with:

select * 
from source
join join1
  on source.key = join1.key

where ...
...
if the number of rows in the result is the same as the source itself (without the join), add the second join and try again. Continue until you find out which join causes the duplicates. Then, add missing predicates to the ON clause. (there probably are missing predicates)

3

It's all three of the tables I marked in my post are the problem tables.
Did some research and now looking into CTE.
I've never tried that before so if anyone can give an example on this certain problem it would help so much.

4

Take the tables one at a time. find out where the duplicates occur. for that join, you will need to add extra predicates.