CODEWITHSUNDEEP
sqlexists
Chase Ernst
Chase Ernst

Reputation: 1155

Getting missing records where the record before and after exists

I am having trouble constructing a query that shows the missing records where the records before and after exists. Example being:

Ticket #
  123
  125

What I would like to return is:

  124

The tables that I am comparing are Tickets and TicketBooks. Tickets house all the used tickets, and TicketBooks house all the Tickets that have been issued out (used and possibly not used). The current query that I am using shows all the missing records.

select TicketNum, TicketBookNum, UnitID, DateIssued, IssuedBy
from TicketBooks
where not exists(select TicketNum
             from Tickets 
             where Tickets.TicketNum=TicketBooks.TicketNum)

When a user issues a TicketBook there are 25 Tickets issued out. So if the first book is issued and the tickets 100-110 are used and 112 has been used I want the query to return this:

Ticket #
  111

I don't want the query to return 113, 114, 115 etc... Any ideas will be greatly appreciated.

Also, the tickets can be issued to multiple units, so one unit can have tickets 1-25 while another unit can have tickets 200-225 issued to them. This prevents me from being able to limit the search to the highest ticket.

The data that is in TicketBooks does not hold the min and max ticket. The values that are inside the table are as follows:

  TicketBooks
  -------------
  TicketBookNum
  TicketNum
  UnitID
  Active

Upvotes: 0

Views: 40

Answers (2)

Abraham B
Abraham B

Reputation: 56

I believe the following query is what you want: 

select TicketNum, TicketBookNum, UnitID, DateIssued, IssuedBy
from TicketBooks
where TicketNum not in (select TicketNum from Tickets) 
AND TicketNum + 1 in (select TicketNum from Tickets) 
AND TicketNum - 1 in (select TicketNum from Tickets)

This query will show you all the "TicketBooks" records which do not have corresponding "Tickets" record and which have "Tickets" records before and after that TicketNum.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271211

If I understand correctly, you want to limit the missing tickets to the ones before the largest used value:

select tb.TicketNum, tb.TicketBookNum, tb.UnitID, tb.DateIssued, tb.IssuedBy
from TicketBooks tb
where not exists (select 1
                  from Tickets t
                  where t.TicketNum = tb.TicketNum
                 ) and
      tb.TicketNum < (select max(tb2.TicketNum) from TicketBooks tb2);

Upvotes: 4

Related Questions