Hi,

I am working on airline reservation/ticketing data and have no control over the underlying database, I can only run queries on it.

I have 3 Tables:

/****** Object:  Table [dbo].[PNR]    Script Date: 24/11/2016 09:09:28 AM ******/

CREATE TABLE [dbo].[PNR](
	[PnrId] [bigint] IDENTITY(1,1) NOT NULL,
	[RecordLocator] [varchar](6) NULL,
	[OfficeID] [varchar](50) NULL,
	[OfficeIATA] [varchar](50) NULL,
	[AgencyName] [varchar](50) NULL,
	[Sign] [varchar](50) NULL,
	[Observation] [varchar](255) NULL,
	[DateCreated] [datetime] NULL,
	[DateUpdated] [datetime] NULL,
	[AppOrgId] [int] NOT NULL,
	[PnrVersionId] [int] NOT NULL,
	[LowestFare] [varchar](255) NULL,
	[HighestFare] [varchar](255) NULL,
	[TripNumber] [varchar](255) NULL,
	[SAPNumber] [varchar](255) NULL,
	[Policy] [varchar](255) NULL,
	[ReasonCode] [varchar](255) NULL,
	[AirJustificationCode] [varchar](255) NULL,
	[AirJustificationText] [varchar](255) NULL,
	[AppliedDiscountCode] [varchar](255) NULL,
	[IsInPolicy] [varchar](255) NULL,
	[FreeText1] [varchar](255) NULL,
	[FreeText2] [varchar](255) NULL,
	[FreeText3] [varchar](255) NULL,
	[FreeText4] [varchar](255) NULL,
	[FreeText5] [varchar](255) NULL,
	[SiteCode] [varchar](10) NULL,
	[WebFareTotal] [varchar](255) NULL,
 CONSTRAINT [PK_Report] PRIMARY KEY CLUSTERED 
(
	[PnrId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PNR] ADD  CONSTRAINT [LM_REPORT]  DEFAULT (getdate()) FOR [DateCreated]
GO

ALTER TABLE [dbo].[PNR] ADD  CONSTRAINT [DF_PNR_DateCreated1]  DEFAULT (getdate()) FOR [DateUpdated]
GO

ALTER TABLE [dbo].[PNR]  WITH CHECK ADD  CONSTRAINT [FK_PNR_PnrVersion] FOREIGN KEY([PnrVersionId])
REFERENCES [dbo].[PnrVersion] ([PnrVersionId])
GO

ALTER TABLE [dbo].[PNR] CHECK CONSTRAINT [FK_PNR_PnrVersion]
GO

/****** Object:  Table [dbo].[TST]    Script Date: 24/11/2016 09:11:25 AM ******/

CREATE TABLE [dbo].[TST](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[BaseFareAmount] [float] NULL,
	[TotalFareAmount] [float] NULL,
	[TaxAmount] [float] NULL,
	[CurrencyBaseFare] [varchar](3) NULL,
	[PnrId] [bigint] NULL,
	[FareBasis] [varchar](250) NULL,
	[CurrencyTotalFare] [varchar](3) NULL,
	[SegmentRPH] [varchar](100) NULL,
	[EquivalentAmount] [float] NULL,
	[EquivalentCurrency] [varchar](3) NULL,
	[SEGMENTTATTOONUMBERS] [varchar](50) NULL,
	[PAXTATTOONUMBERS] [varchar](50) NULL,
 CONSTRAINT [PK_TST] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[TST]  WITH CHECK ADD  CONSTRAINT [FK_TST_PNR] FOREIGN KEY([PnrId])
REFERENCES [dbo].[PNR] ([PnrId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[TST] CHECK CONSTRAINT [FK_TST_PNR]
GO


/****** Object:  Table [dbo].[Ticket]    Script Date: 24/11/2016 09:10:42 AM ******/

CREATE TABLE [dbo].[Ticket](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[TicketNumber] [varchar](50) NULL,
	[PnrId] [bigint] NULL,
	[TicketDate] [date] NULL,
	[AirlineTktCode] [varchar](50) NULL,
	[TicketType] [varchar](3) NULL,
	[IssuerCode] [varchar](15) NULL,
	[FreeTex] [varchar](250) NULL,
	[AssociatedSegmentTattooNumbers] [varchar](50) NULL,
	[ASSOCIATEDPAXTATTOONUMBERS] [varchar](50) NULL,
 CONSTRAINT [PK_Ticket] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Ticket]  WITH CHECK ADD  CONSTRAINT [FK_Ticket_PNR] FOREIGN KEY([PnrId])
REFERENCES [dbo].[PNR] ([PnrId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Ticket] CHECK CONSTRAINT [FK_Ticket_PNR]
GO

The relationships between the tables are:

A PNR is related to the TST and Ticket tables through:

PNR.PNRId = TST.PNRId

PNR.PNRId = Ticket.PNRId


The TST table is related to the Ticket table through:

Ticket.pnrid = tst.pnrid
and Ticket.associatedsegmenttattoonumbers = tst.segmenttattoonumbers
and Ticket.associatedpaxtattoonumbers = tst.paxtattoonumbers

The problem with the latter relationships is that the values in the TST.PaxTattooNumbers might be "1,2,3" while in the Ticket.AssociatedPaxTattooNumbers we might have 3 records , one with each of those values of 1, 2 and 3. Like this

TST Table:

TSTId   PNRId   PaxTattooNumbers
1       1       1,2,3

Ticket Table:

TicketId   PnrId    AssociatedPaxTattooNumbers
1          1        1
2          1        2
3          1        3

The PaxtattooNumbers represent passengers and a passenger can be a child which will share a ticket with a regular passenger so it is also possible to have ticket records like this:

TicketId   PnrId    AssociatedPaxTattooNumbers
1          1        1,2
2          1        3

In this case 2 represents a child, so only 2 tickets were issued.

My challenge is that I need to find all the PNR.PNRIds that have not been fully ticketed. That is each TST has a corresponding record in the ticket table for all passengers.

This was my initial query when I didn't realise the PaxTattooNumbers got split up on the Ticket table:

select pnrid
      ,[SEGMENTTATTOONUMBERS]
      ,[PAXTATTOONUMBERS]
from tst
where not exists (select pnrid
						,associatedsegmenttattoonumbers
						,associatedpaxtattoonumbers
					from ticket
					where pnrid = tst.pnrid 
						and associatedsegmenttattoonumbers = segmenttattoonumbers
						and associatedpaxtattoonumbers = paxtattoonumbers)
	order by pnrid

How would I adjust this to handle the splitting up of the PaxTattooNumbers?