Chase Ernst Chase Ernst - 1 year ago 70
SQL Question

SQL Command Returning Unexpected Results

I am having issues with a sql procedure I wrote quite some time ago. The procedure is designed to generate a report that displays all missing records within our database.

For example, if ticket numbers 10, 11, and 13 have been used, then the procedure should return ticket number 12 on the report.

We manage each ticket book and issue them out accordingly when a person takes one. When the ticket book is issued, all 25 tickets in the book are created in the database, and when each individual ticket is entered into the database, the ticket is marked as used. This is how the procedure checks what tickets are used and not used.

This is the current procedure we are utilizing

create procedure FindMissingTickets
select TicketNum, TicketBookNum, UnitID, DateIssued, IssuedBy
from TicketBooks a
where a.Used='No'
and a.TicketNum < (select MAX(b.TicketNum)
from TicketBooks b
where b.Used='Yes'
and b.UnitID=a.UnitID
and b.BookType='Truck' or b.BookType='Work')
and a.BookType='Truck' or a.BookType='Work'
order by TicketNum desc

This is a quick example of how our database table is laid out:
enter image description here

So in theory, the ticket number 525 should be shown on this report. What is actually is happening is all of the records from ticket number 493 (the first ticket in the database) to 550 (the last ticket of this type) are shown in the report, even though that all of them except 525 are set the

I don't fully understand how this procedure could return any records that have the column
set to

Any help is greatly appreciated.

Answer Source

It's just missing parentheses, you need to place parentheses around the last part of your where clause:

and (a.BookType='Truck' or a.BookType='Work')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download