Return to Question

formatting, tags

edit approved Sep 8, 2014 at 13:03

2.1k
3
22
28

SELECT T_ActionTicketLog.ActionTicketID,  ,T_ActionTicketLog.BarCode,  ,T_ActionTicketLog.UserID,   ,T_TicketStatus.Name, T_OrderTicket.OrderID FROM  ,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

SELECT T_ActionTicketLog.ActionTicketID,  ,T_ActionTicketLog.BarCode,  ,T_ActionTicketLog.UserID,   ,T_TicketStatus.Name,  ,T_OrderTicket.OrderID, T_TicketPrint.TicketBarCode FROM  ,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

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

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

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

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

Source Link

asked Sep 8, 2014 at 11:50

tesicg

4.1k
16
70
129

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?

sql-server

Collectives™ on Stack Overflow

Return to Question

Duplicate records in query result