CODEWITHSUNDEEP
sql-servert-sql
tesicg
tesicg

Reputation: 4053

Duplicate records in query result

Here's the query that works:

SELECT  T_ActionTicketLog.ActionTicketID
     ,T_ActionTicketLog.BarCode
     ,T_ActionTicketLog.UserID
     ,T_TicketStatus.Name
     ,T_OrderTicket.OrderID
FROM T_ActionTicketLog 
INNER JOIN T_TicketStatus 
    ON T_ActionTicketLog.StatusID = T_TicketStatus.ID
LEFT OUTER JOIN T_OrderTicket 
    ON T_ActionTicketLog.TicketOrderID = T_OrderTicket.ID
where T_ActionTicketLog.ActionTicketID = 21780101

There are 27 records returned, which is ok.

But, I want to add one more field to result set in this way:

SELECT  T_ActionTicketLog.ActionTicketID
     ,T_ActionTicketLog.BarCode
     ,T_ActionTicketLog.UserID
     ,T_TicketStatus.Name
     ,T_OrderTicket.OrderID
     ,T_TicketPrint.TicketBarCode
FROM T_ActionTicketLog 
INNER JOIN T_TicketStatus 
    ON T_ActionTicketLog.StatusID = T_TicketStatus.ID
LEFT OUTER JOIN T_OrderTicket 
    ON T_ActionTicketLog.TicketOrderID = T_OrderTicket.ID
LEFT OUTER JOIN T_TicketPrint 
    ON T_OrderTicket.ActionTicketID = T_TicketPrint.ActionTicketID
where   T_ActionTicketLog.ActionTicketID = 21780101

There are 165 records returned, which is wrong.

The additional left outer join makes the issue.

The tables:

CREATE TABLE [T_ActionTicketLog](
    [ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [ActionTicketID] [bigint] NULL,
    [TicketOrderID] [bigint] NULL,
    [StatusID] [tinyint] NULL,
    [UserID] [int] NULL,
    [SalerID] [int] NULL,
    [FiscalID] [int] NULL,
    [BarCode] [bigint] NULL,
    [ReservDate] [datetime] NULL,
    [Created] [datetime] NULL,
    [Comments] [varchar](50) NULL,

CREATE TABLE [T_TicketStatus](
    [ID] [tinyint] NOT NULL,
    [Name] [varchar](50) NULL,
    [Created] [datetime] NULL,


CREATE TABLE [T_TicketPrint](
    [ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [CashierID] [int] NULL,
    [BarCode] [bigint] NULL,
    [ControlDigit] [tinyint] NULL,
    [ActionTicketID] [bigint] NULL,
    [Created] [datetime] NULL,
    [CancelDate] [datetime] NULL,
    [TicketBarCode] [varchar](250) NULL,
    [OrderTicketID] [bigint] NULL,
    [SetId] [bigint] NULL,

CREATE TABLE [T_OrderTicket](
    [ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [OrderID] [int] NULL,
    [ActionTicketID] [bigint] NULL,
    [Status] [smallint] NULL,
    [Created] [datetime] NULL,
    [UserID] [int] NULL,

How to add the additional field without duplicating records?

Upvotes: 1

Views: 78

Answers (3)

Jordan Parker
Jordan Parker

Reputation: 1236

There are a few ways you could go about this (grouping, subquery, etc), here is the grouping route:

SELECT  T_ActionTicketLog.ActionTicketID
    ,T_ActionTicketLog.BarCode
    ,T_ActionTicketLog.UserID
    ,T_TicketStatus.Name
    ,T_OrderTicket.OrderID
    ,MAX(T_TicketPrint.TicketBarCode)
FROM T_ActionTicketLog 
INNER JOIN T_TicketStatus
    ON T_ActionTicketLog.StatusID = T_TicketStatus.ID
LEFT OUTER JOIN T_OrderTicket 
    ON T_ActionTicketLog.TicketOrderID = T_OrderTicket.ID
LEFT OUTER JOIN T_TicketPrint 
    ON T_OrderTicket.ActionTicketID = T_TicketPrint.ActionTicketID
WHERE   T_ActionTicketLog.ActionTicketID = 21780101
GROUP BY T_ActionTicketLog.ActionTicketID
    ,T_ActionTicketLog.BarCode
    ,T_ActionTicketLog.UserID
    ,T_TicketStatus.Name
    ,T_OrderTicket.OrderID

You may need to change the MAX() aggregate to something else that makes more sense depending on how multiple TicketPrints associated with an OrderTicket need to be handled.

Upvotes: 0

Vikram Jain
Vikram Jain

Reputation: 5588

Your query return 165 record because T_TicketPrint have multiple entry for single ticket. So, you need a single record for ticket from T_TicketPrint table.

For that i using group by ActionTicketID in T_TicketPrint and get barcode. Your query look like and change as per requirement:

SELECT  T_ActionTicketLog.ActionTicketID, T_ActionTicketLog.BarCode, T_ActionTicketLog.UserID,
      T_TicketStatus.Name, T_OrderTicket.OrderID, A.TicketBarCode
FROM   T_ActionTicketLog INNER JOIN T_TicketStatus ON T_ActionTicketLog.StatusID = T_TicketStatus.ID
LEFT OUTER JOIN T_OrderTicket ON T_ActionTicketLog.TicketOrderID = T_OrderTicket.ID
LEFT OUTER JOIN 
(select TicketBarCode, ActionTicketID from T_TicketPrint 
group by ActionTicketID,TicketBarCode) as A ON T_OrderTicket.ActionTicketID = A.ActionTicketID
where   T_ActionTicketLog.ActionTicketID = 21780101

Upvotes: 1

GarethD
GarethD

Reputation: 69749

I think the problem is that you have multipe records in T_TicketPrint for each record in T_OrderTicket but you only want to display one of these records, in which case I would change the LEFT JOIN to an OUTER APPLY, and just select the top 1 record:

SELECT  T_ActionTicketLog.ActionTicketID, 
        T_ActionTicketLog.BarCode, 
        T_ActionTicketLog.UserID,
        T_TicketStatus.Name, 
        T_OrderTicket.OrderID, 
        tp.TicketBarCode
FROM    T_ActionTicketLog 
        INNER JOIN T_TicketStatus 
            ON T_ActionTicketLog.StatusID = T_TicketStatus.ID
        LEFT OUTER JOIN T_OrderTicket 
            ON T_ActionTicketLog.TicketOrderID = T_OrderTicket.ID
        OUTER APPLY
        (   SELECT  TOP 1 TicketBarCode
            FROM    T_TicketPrint 
            WHERE   T_OrderTicket.ActionTicketID = T_TicketPrint.ActionTicketID
            ORDER BY T_TicketPrint.Created DESC -- NEWEST RECORD
        ) AS tp
WHERE   T_ActionTicketLog.ActionTicketID = 21780101

Since you could have multiple records in T_TicketPrint the real question then becomes which one should you pick? In the example above I have assumed the latest one, but if this is not correct just change the order by clause in the OUTER APPLY.

Upvotes: 1

Related Questions