Delete select value comparing to another table

Question

Delete select value comparing to another table

Navigation

#1 by (2 votes)

3

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

Explaining: There is a schedule Agendamento´, onde fica registrado o horário de inicio horainicia e fim horafim 'of the schedule, example I have scheduled for day 17 a time from 10 in the morning until 10:30 in the morning.

And there is the horariosdodia table which is the time of day that starts from 7 in the morning and in 10 and 10 minutes it goes until 18 at night. Column called 'free_hours'.

My big problem is to delete and show only the available times when comparing horainicio with horarios_livres and delete the tbm hours of 10:10, 10:20 as it is marked from 10 to 10:30 the times of 10:10 and 10:20 should not appear.

I tried for sql plus my knowledge was not enough to be able to create a select to perform such an operation, I tried via php already direct in the application but also could not, and there I need a help from you .

Insert for table_time_tables:

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);

php mysql sql phpmyadmin

asked by anonymous 19.11.2016 / 14:45

1 answer

Calculate the output of an operation between two dataframes conditionally How to get only one git log result between the given range?

score 2 · Accepted Answer

Perform a NOT EXISTS check against the agendamento table, verifying that the horarios_livres column is not between horainicia and horafim by 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'))