SQL Get a result comparing aggregate functions

Question

I have a table that looks like this:

code int, primary key
reservation_code int,
indate date,
outdate date,
slot int,
num int,

The database has a somewhat weird design to it, the way it is supposed to work is that this table keeps the dates of every slot booking, num is used to keep track of contiguous bookings for what I believe are legacy reasons.

I'm trying to come up with a query that checks for prior bookings in the database. My idea of doing this:

For a given slot number, check if there is a group of rows with the same reservation_code which on the row with the minimum num value of the group has an indate date that is lower or equal than a given date and the outdate date on the row with the max num value is higher than the same given date.

My closest approach to this in SQL:

EDIT: With help from Barmar

SELECT b.reservation_code
FROM bookings b
JOIN (SELECT reservation_code, MIN(num) minnum
      FROM bookings
      WHERE slot = "given_slot"
      AND indate <= "given_date"
      GROUP BY reservation_code) min
ON minnum = num and b.reservation_code = min.reservation_code
JOIN (SELECT reservation_code, MAX(num) maxnum
      FROM bookings
      WHERE slot = "given_slot"
      AND outdate > "given_date"
      GROUP BY reservation_code) max
ON maxnum = num and b.reservation_code = max.reservation_code
WHERE slot="given_slot"
AND indate <= "given_date"
AND outdate > "given_date"
GROUP BY b.reservation_code

Adding GROUP BY to both subqueries makes it work for most cases, but the second check still returns a wrong answer.

Here are some sample rows and queries to try to make the question a bit clearer:

sample rows:

code    reservation_code    indate      outdate     slot    num
1       1                   01/01/13    03/01/13    1       0
2       1                   03/01/13    05/01/13    1       1
3       1                   05/01/13    10/01/13    1       2
4       2                   04/01/13    15/01/13    2       0
5       2                   15/01/13    19/01/13    2       1
6       3                   11/01/13    13/01/13    1       0
7       4                   15/01/13    16/01/13    3       0
8       5                   01/01/13    15/01/13    3       0
9       5                   15/01/13    25/01/13    4       1

Sample checks:

slot 2, date 21/02/13, should return not booked.
slot 2, date 16/01/13, should return booked
slot 1, date 14/01/13, should return not booked
slot 1, date 12/01/13, should return booked
slot 1, date 10/01/13, should return not booked
slot 3, date 02/01/13, should return booked
slot 4, date 15/01/13, should return booked
slot 4, date 25/01/13, should return not booked

I'm having trouble trying to understand the spec ("*For a given slot number, check if there is a group of rows with...*"). Given that most cut-and-dried MySQL query questions are answered within a couple minutes, I think others may be having trouble too. Could you provide a few sample rows and expected output? — Ed Gibbs, May 31 '13 at 18:17
I'm not certain from your question, but I think you are trying to emulate window functions in mysql. As far as I know this isnt possible out of the box, but some clever people have other ideas... http://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql, if you search for sql window functions(so/google) you'll find some details on the terminology of what you are looking for. Hope that helps. — Sumit, May 31 '13 at 18:29
I will fetch a few sample rows and output to make the problem clearer, that was my best attempt at describing it but I understand it's not as well explained as one could hope for. — user1090729, May 31 '13 at 18:34

Barmar · Answer 1 · 2013-06-01T03:02:21.443

2

You need to use a JOIN with the aggregated table

SELECT b.reservation_code, count(1)
FROM bookings b
JOIN (SELECT reservation_code, MAX(num) maxnum
      FROM bookings
      WHERE slot = "given slot"
      AND indate <= "given date"
      GROUP BY reservation_code) m
ON maxnum = num and b.reservation_code = m.reservation_code
WHERE slot="given slot"
AND indate <= "given date"
GROUP BY b.reservation_code

edited Jun 01 '13 at 03:02

answered May 31 '13 at 18:17

Barmar

741,623
53
500
612

This is very interesting, I hadn't looked into JOIN before.Could this also be used to at the same time check for outdates that are greater than the given date on min(num)? – user1090729 May 31 '13 at 18:31
Yes, it probably needs to be another subquery that you join with. P.S. SQL programmers who don't know about JOIN are missing 90% of the value of database programming. – Barmar May 31 '13 at 18:38
I'm deeply sorry but I don't seem to be able to get it to return the proper result. I have updated my query in the question, could you point out what am I doing wrong? – user1090729 Jun 01 '13 at 03:00
Just noticed that I was missing a GROUP BY in the subquery. I've added it to my answer, you need to add it to both your subqueries. – Barmar Jun 01 '13 at 03:02
Thank you, adding GROUP BY to both subqueries greatly improved my results, my second check however still returns not booked when it should return booked. This is a bit puzzling, I'll come up with more checks to see what's happening more clearly. – user1090729 Jun 01 '13 at 03:17

score 0 · Accepted Answer · answered Jun 02 '13 at 13:14

After a good night's sleep I realized my problem was rather trivial and could be solved with a very simple query such as:

SELECT 1
FROM bookings
WHERE slot="given slot"
AND indate <= "given date"
AND outdate > "given date"

I want to thank everyone who tried to help me, I'm sorry I wasted your time with this.

SQL Get a result comparing aggregate functions

2 Answers2