SQL filter (Result that should not appear)

Asked

Viewed 66 times

1

is the following I am creating an online schedule and need to list the and show the times that are not scheduled, to show the schedules free. And for that I created the schedule_schedules table where in the fixed schedule_hours column is the list of schedules, and in the other table the schedule_schedules is where the schedules are and I need the value that is in the schedule_schedules table in the case in the fixed schedule_hours column, only appear times that have not been scheduled, and the problem that is happening that appears example:

I need the values of a table with fixed times not to appear in select.

The fixed times are: 02:00 02:10 02:20 02:30

Then one person scheduled the time from 02:00 to 02:30, then the time of 02:10, 02:20 are appearing on select and should not.

follow the query I made:

SELECT horariosfixos 
FROM agendamento_horarios 
WHERE horariosfixos NOT IN(SELECT horaInicio 
                           FROM agendamento 
                           WHERE data = '17-10-2016' 
                           UNION 
                           SELECT horaFim 
                           FROM agendamento 
                           WHERE data = '17-10-2016')

If you can help me, I’m grateful.

php mysql sql phpmyadmin

  • When you do a NOT IN search, you are doing an exact search. You are matching the values. In your case, I believe you will need to use the BETWEEN, to search between two values.

2 answers

0


I believe this SELECT can help you solve:

SELECT Ah.horariosfixos 
FROM agendamento_horarios Ah
LEFT OUTER JOIN agendamento Ag
  ON Ag.horaInicio <= Ah.horariosfixos 
  AND Ah.horariosfixos < Ag.horaFim
  AND Ag.data = '17-10-2016'
WHERE Ag.horaInicio IS NULL

  • It has really helped a lot, did not show the schedules between scheduling, but the schedules after scheduling also does not show. Type 02:40 does not appear when I made the select and 02:30 in the case since it is the end time should appear because from that time could already ta attending, but thank you Rick, but it is very difficult this logic.

  • I edited the answer by removing the "=" from the Where of the timeFim. Maybe this will make the 02:30 appear.

  • The logic was reversed. You can see if it now works?

  • Unfortunately those didn’t work:

  • only showed: hours 02:10:00 02:20:00 02:30:00

  • continued to show 02:10

  • I edited again. Still not working? This field data is with the day 17/10 same?

  • Still doesn’t work. Shows the 02:10, 02:20 and 02:30

Show 3 more comments

-1

Rick thanks for the help, after you sent me the code you made, gave me a light, and then I set up the code and it worked, follow me:

SELECT Ah.hoursFixs FROM scheduling_schedules Ah, scheduling Ag Where ag.hoursInicio<>ah.hoursFixos and ag.hoursFim<=ah.hoursFixos

Browser other questions tagged php mysql sql phpmyadmin

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