0

I used this query: 

SELECT 
  tavolo.ID_Tavolo
FROM 
  tavolo
  LEFT JOIN prenota 
    ON tavolo.ID_Tavolo = prenota.ID_Tavolo
WHERE 
  tavolo.ID_Tavolo NOT IN 
    (SELECT 
      Prenota.ID_Tavolo 
     FROM 
      prenota) 
  OR tavolo.ID_Tavolo IN 
    (SELECT 
      prenota.ID_Tavolo 
     FROM 
       prenota 
     WHERE 
       Data_Pren != '2020-06-04' 
       OR Data_Pren = '2020-06-04' 
       AND Ora_Pren NOT BETWEEN '19:00:00' AND '20:30:00' 
       AND ADDTIME(Ora_Pren, "1:30:00.000000") NOT BETWEEN '19:00:00' AND '20:30:00')
ORDER BY Tavolo.ID_Tavolo

It worked when there was no data in the database but, after creating 3 reservations (I have 3 tables), it started using the same ID every time (7) not checking the date.

Theese are my tables:

Prenota table (structure)

enter image description here

Prenota table (data)

enter image description here

Tavolo table (structure)

enter image description here

Tavolo table (data)

enter image description here

(The query works by checking:

1) If the ID doesn't exist in the table "prenota" give me the ID;

2) If the ID already exists and the date is different OR the date is the same but the hour IS NOT BETWEEN the registration hour and +1h30m AND adding 1h30m to check if all the tables are still unavailable during that hour.

Here's a screen of me trying the query:

enter image description here

I simply want to know how to make a reservation for a table working by checking:

1) The ID of the table

2) The Date of the already reserved tables

3) The Time of the already booked tables

4) Make a reservation if a "gap" is found checking the time chosen by the user and the time +1h30m (the max time that you can stay at the table)

Here's the link to try it: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=87563a5b3f14efee1ffc535e14675f06

black9807
  • 11
  • 7
  • we'll need to at least see all the data in the table(s) before we can realistically suggest anything. And also please be clear about what results you were expecting instead of what you are currently seeing. And if you can please take a few moments to put this in something like [DBFiddle](https://dbfiddle.uk/?rdbms=mysql_8.0) as a runnable demo then it will be a lot easier for people to try out queries and help you find a solution. – ADyson Jun 05 '20 at 10:41
  • Thank you, how can you use DBFiddle with AUTO_INCREMENT tho? – black9807 Jun 05 '20 at 19:32
  • Also, I tried it like this https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=32d0ddc4bea424cd3341d3ab3a911fd6 and it doesn't work very well – black9807 Jun 05 '20 at 19:33
  • 1) you called your table `Tavolo` but then refer to it in the query as `tavolo`. MySQL is case-sensitive, so that won't work. Same for the other tables as well 2) you use AUTO_INCREMENT In DbFiddle [exactly the same way that you use it anywhere else](https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html) - MySQL's syntax isn't different. I'm not really sure why you think this is a problem? 3) you haven't added any data to the DbFiddle so it's not much use for testing your query! – ADyson Jun 05 '20 at 20:16
  • 4) Cognome is a bad choice for the primary key of the client. Surely in your life you have met two people with the same name?? Use a unique auto-increment ID instead. 5) prenota also needs a proper ID field, I'm not sure why you had that as varchar in the DbFiddle. 6) you forgot the Ora_Pren column entirely. Please check your work more carefully! – ADyson Jun 05 '20 at 20:19
  • After fixing the case, I couldn't find Ora_Pren defined anywhere in the fiddle. – Honeyboy Wilson Jun 05 '20 at 20:19
  • Here's a version which fixes all those problems: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9b1942c6d60f621f36590e2b257a90ea . Now please add some data to it so we can help with the actual question, instead of just basic syntax and proof-reading. Thanks. – ADyson Jun 05 '20 at 20:21
  • Thank you so much for all of your help, I'm used to PHPMyAdmin that isn't case sensitive with tables – black9807 Jun 05 '20 at 20:29
  • No problem. Now please add some data and give us a new link to the updated DbFiddle, so we can help – ADyson Jun 05 '20 at 20:54
  • P.S. this query is essentially a variation of the "overlapping dates" problem which is quite well known. The basic underlying solution is [this one](https://stackoverflow.com/a/2546046/5947043) - see if you can adapt it to your needs. – ADyson Jun 05 '20 at 20:58
  • I added everything (I changed something to make it work well), thank you for the link but my problem is also on time data (not only date data), I'm trying to make the query recognize when there is a gap to make the reservation – black9807 Jun 05 '20 at 22:40
  • The exact same principle applies when you include time, though. It's the same idea but just with a more precise value. – ADyson Jun 05 '20 at 22:44
  • P.S. in your database I think it would probably make more sense to store the date and time all together in one field, since that is a single piece of information about a point in time where the reservation is happening. It will make it much easier to make comparisons between different times in order to check for overlaps. – ADyson Jun 05 '20 at 22:49
  • Yeah, I've already tried the method that you linked, I think that I'm going to change my database, thanks for all the help, can you close this question or do I have to delete it? – black9807 Jun 06 '20 at 10:36
  • No need to do either really, especially if you haven't actually solved it yet - me or someone else might have time to come along and have a go at it. We've got the question into an answerable state so it would make no sense to delete it now – ADyson Jun 06 '20 at 12:10
  • I just want to deleted because, after sleeping and thinking about it, I'm going to be doing it manually. The restaurant will receive the reservation, check if there is a gap and eventually confirm it – black9807 Jun 06 '20 at 12:46

0 Answers0