php – Exclude select value by comparing with another table

Question:

Good morning, I'm developing a scheduling website, however, I'm having a huge difficulty trying to display only the times that are available.

Explaining: There is the Agendamento´, onde fica registrado o horário de inicio table Agendamento´, onde fica registrado o horário de inicio time e fim Agendamento´, onde fica registrado o horário de inicio , for example I have scheduled for the 17th a time from 10 am to 10:30 am.

And there is the table horariosdodia which are the time of day that starts from 7 am and 10:10 minutes will until 18 at night. Column called 'free_times`.

My big problem is to remove and only shows the time available when comparing horainicio with horarios_livres and delete the TBM time 10:10, 10:20 since it is marked 10 10:30 the hours of 10:10 and 10:20 should not appear.

I tried using sql but my knowledge was not enough to be able to create a select to perform such an operation, I tried via php already directly in the application but I couldn't, and then I need some help from you.

Insert for the free_hours table:

INSERT INTO horariosdodia(horarios_livres) VALUES (070000);
INSERT INTO horariosdodia(horarios_livres) VALUES (071000);
INSERT INTO horariosdodia(horarios_livres) VALUES (072000);
INSERT INTO horariosdodia(horarios_livres) VALUES (073000);
INSERT INTO horariosdodia(horarios_livres) VALUES (074000);
INSERT INTO horariosdodia(horarios_livres) VALUES (075000);
INSERT INTO horariosdodia(horarios_livres) VALUES (080000);
INSERT INTO horariosdodia(horarios_livres) VALUES (081000);
INSERT INTO horariosdodia(horarios_livres) VALUES (082000);
INSERT INTO horariosdodia(horarios_livres) VALUES (083000);
INSERT INTO horariosdodia(horarios_livres) VALUES (084000);
INSERT INTO horariosdodia(horarios_livres) VALUES (085000);
INSERT INTO horariosdodia(horarios_livres) VALUES (090000);
INSERT INTO horariosdodia(horarios_livres) VALUES (091000);
INSERT INTO horariosdodia(horarios_livres) VALUES (092000);
INSERT INTO horariosdodia(horarios_livres) VALUES (093000);
INSERT INTO horariosdodia(horarios_livres) VALUES (094000);
INSERT INTO horariosdodia(horarios_livres) VALUES (095000);
INSERT INTO horariosdodia(horarios_livres) VALUES (100000);
INSERT INTO horariosdodia(horarios_livres) VALUES (101000);
INSERT INTO horariosdodia(horarios_livres) VALUES (102000);
INSERT INTO horariosdodia(horarios_livres) VALUES (103000);
INSERT INTO horariosdodia(horarios_livres) VALUES (104000);
INSERT INTO horariosdodia(horarios_livres) VALUES (105000);
INSERT INTO horariosdodia(horarios_livres) VALUES (110000);
INSERT INTO horariosdodia(horarios_livres) VALUES (111000);
INSERT INTO horariosdodia(horarios_livres) VALUES (112000);
INSERT INTO horariosdodia(horarios_livres) VALUES (113000);
INSERT INTO horariosdodia(horarios_livres) VALUES (114000);
INSERT INTO horariosdodia(horarios_livres) VALUES (115000);
INSERT INTO horariosdodia(horarios_livres) VALUES (120000);
INSERT INTO horariosdodia(horarios_livres) VALUES (121000);
INSERT INTO horariosdodia(horarios_livres) VALUES (122000);
INSERT INTO horariosdodia(horarios_livres) VALUES (123000);
INSERT INTO horariosdodia(horarios_livres) VALUES (124000);
INSERT INTO horariosdodia(horarios_livres) VALUES (125000);
INSERT INTO horariosdodia(horarios_livres) VALUES (130000);
INSERT INTO horariosdodia(horarios_livres) VALUES (131000);
INSERT INTO horariosdodia(horarios_livres) VALUES (132000);
INSERT INTO horariosdodia(horarios_livres) VALUES (133000);
INSERT INTO horariosdodia(horarios_livres) VALUES (134000);
INSERT INTO horariosdodia(horarios_livres) VALUES (135000);
INSERT INTO horariosdodia(horarios_livres) VALUES (140000);
INSERT INTO horariosdodia(horarios_livres) VALUES (141000);
INSERT INTO horariosdodia(horarios_livres) VALUES (142000);
INSERT INTO horariosdodia(horarios_livres) VALUES (143000);
INSERT INTO horariosdodia(horarios_livres) VALUES (144000);
INSERT INTO horariosdodia(horarios_livres) VALUES (145000);
INSERT INTO horariosdodia(horarios_livres) VALUES (150000);
INSERT INTO horariosdodia(horarios_livres) VALUES (151000);
INSERT INTO horariosdodia(horarios_livres) VALUES (152000);
INSERT INTO horariosdodia(horarios_livres) VALUES (153000);
INSERT INTO horariosdodia(horarios_livres) VALUES (154000);
INSERT INTO horariosdodia(horarios_livres) VALUES (155000);
INSERT INTO horariosdodia(horarios_livres) VALUES (160000);
INSERT INTO horariosdodia(horarios_livres) VALUES (161000);
INSERT INTO horariosdodia(horarios_livres) VALUES (162000);
INSERT INTO horariosdodia(horarios_livres) VALUES (163000);
INSERT INTO horariosdodia(horarios_livres) VALUES (164000);
INSERT INTO horariosdodia(horarios_livres) VALUES (165000);
INSERT INTO horariosdodia(horarios_livres) VALUES (170000);
INSERT INTO horariosdodia(horarios_livres) VALUES (180000);
INSERT INTO horariosdodia(horarios_livres) VALUES (181000);
INSERT INTO horariosdodia(horarios_livres) VALUES (182000);
INSERT INTO horariosdodia(horarios_livres) VALUES (183000);
INSERT INTO horariosdodia(horarios_livres) VALUES (184000);
INSERT INTO horariosdodia(horarios_livres) VALUES (185000);
INSERT INTO horariosdodia(horarios_livres) VALUES (190000);
INSERT INTO horariosdodia(horarios_livres) VALUES (191000);
INSERT INTO horariosdodia(horarios_livres) VALUES (192000);
INSERT INTO horariosdodia(horarios_livres) VALUES (193000);
INSERT INTO horariosdodia(horarios_livres) VALUES (194000);
INSERT INTO horariosdodia(horarios_livres) VALUES (195000);
INSERT INTO horariosdodia(horarios_livres) VALUES (200000);
INSERT INTO horariosdodia(horarios_livres) VALUES (201000);
INSERT INTO horariosdodia(horarios_livres) VALUES (202000);
INSERT INTO horariosdodia(horarios_livres) VALUES (203000);
INSERT INTO horariosdodia(horarios_livres) VALUES (204000);
INSERT INTO horariosdodia(horarios_livres) VALUES (205000);
INSERT INTO horariosdodia(horarios_livres) VALUES (210000);
INSERT INTO horariosdodia(horarios_livres) VALUES (211000);
INSERT INTO horariosdodia(horarios_livres) VALUES (212000);
INSERT INTO horariosdodia(horarios_livres) VALUES (213000);
INSERT INTO horariosdodia(horarios_livres) VALUES (214000);
INSERT INTO horariosdodia(horarios_livres) VALUES (215000);
INSERT INTO horariosdodia(horarios_livres) VALUES (220000);

Answer:

Perform a check NOT EXISTS regarding table agendamento , checking if the column horarios_livres is not among the horainicia and horafim subtracting 1 second.

SELECT d.*
  FROM horariosdodia d
 WHERE NOT EXISTS(SELECT *
                    FROM agendamento a
                   WHERE d.horarios_livres BETWEEN a.horainicia AND SUBTIME(a.horafim, '0 0:0:1.00000'))
Scroll to Top