Delete select value by comparing to another table

Asked

Viewed 103 times

3

Good morning, I am developing a scheduling site, however, I am having a huge difficulty trying to display only the schedules that are available.

Explaining: There is the table Agendamento´, onde fica registrado o horário de iniciotimetablee fimSchedule` of scheduling, example I have scheduled for day 17 a time from 10 am until 10:30 am.

And there’s the table horariosdodia which are the hours of the day that starts at 7 am and in 10 and 10 minutes goes until 18 pm. Column call of free_hours`.

My big problem is eliminating and shows only available times when comparing horainicio with horarios_livres and exclude the tbm times of 10:10, 10:20 since it is marked from 10 to 10:30 the times of 10:10 and 10:20 should not appear.

I tried for the sql more my knowledge was not enough to manage to create a select to carry out such an operation, I tried to php already direct in the application but also could not, and then I need a help from you.

Insert to the 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);
php mysql sql database phpmyadmin

  • You want what’s in the free schedules table and not in the schedule, this?

  • This I need to appear what is in the schedule_free appear and the schedules that are in the schedule do not appear, only the final value of the schedule should actually appear by logic.

  • Okay, is it sql server? What are the data types of the two columns?

  • ta in mysql, the data type is time

  • Do you have at least the Inserts to popular the schedule table free? If you edit the question and post please

  • I added the Insert

  • Okay, I’ll mount the select and post on the reply

  • OK, thank you very much.

Show 3 more comments

1 answer

2


Perform a check NOT EXISTS with respect to table agendamento, checking whether the column horarios_livres is not between the horainicia and the 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'))
  • 1

    Very good, congratulations. Who knows. It worked perfectly, that’s what I wanted. Thank you

  • Just explain one thing to me, because you subtracted a second?

  • Otherwise the 8:00 hours wouldn’t show up if you had a 7:30 to 8:00 appointment. In fact I think that in scheduling it is better if you always mark with 1 minute less. It just has to see if it doesn’t look weird

  • 1

    Got it, thanks Sorack.

Browser other questions tagged php mysql sql database phpmyadmin

You are not signed in. Login or sign up in order to post.