CODEWITHSUNDEEP
sql
Ryan NZ
Ryan NZ

Reputation: 626

Equation in SQL query

I am trying to identify duplicate data within our database.

If somebodys first seat is 1, and their last seat is 3, it means they should have x3 num_seats

So i want to run a query where first_seat - last_seat +1 = num_seats. Any pointers on what is wrong with my query?

select acct_id, event_id, event_name, section_name, row_name, first_seat, last_seat, num_seat
from dba.v_event
where first_seat - last_seat +1 != num_seat

Upvotes: 1

Views: 112

Answers (1)

Barmar
Barmar

Reputation: 780984

Your equation is backward. It should be last_seat - first_seat + 1.

In your example, first_seat = 1 and last_seat = 3. 

first_seat - last_seat + 1 = 1 - 3 + 1 = -1
last_seat - first-seat + 1 = 3 - 1 + 1 = 3

If you want to allow the seats to be listed in either order, you can use ABS() to get the absolute value of the difference:

ABS(last_seat - first_seat) + 1

Upvotes: 7

Related Questions