1

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?

Improve this question
1
  • Could you post some sample data? Commented Sep 8, 2014 at 11:53

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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
Improve this answer

1 Comment

if barcode is same no need to group by static value column.
0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.