Select command doesn't return anything

Question

I need to show the hours avaible on each doctor but my code returns nothing Here's my code:

SELECT DISTINCT Hours
FROM agenda_hours, doctor
WHERE Agenda_Hours.idHours NOT IN (SELECT DISTINCT appoitment_hour.Hour FROM appoitment_hour)
AND Doctor.Name IN (SELECT Doctor.Name FROM Doctor Where name = "Name");

This is the description of the table

+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| id            | int(11)      | NO   | PRI | NULL    |       |
| Name          | varchar(100) | NO   |     | NULL    |       |
| CRM           | varchar(20)  | NO   |     | NULL    |       |
| Tel           | varchar(10)  | YES  |     | NULL    |       |
| Cel           | varchar(10)  | YES  |     | NULL    |       |
| RG            | varchar(40)  | YES  |     | NULL    |       |
| CPF           | varchar(50)  | YES  |     | NULL    |       |
| Street        | varchar(120) | YES  |     | NULL    |       |
| num           | varchar(7)   | YES  |     | NULL    |       |
| bairro        | varchar(40)  | YES  |     | NULL    |       |
| city          | varchar(70)  | YES  |     | NULL    |       |
| zip           | varchar(13)  | YES  |     | NULL    |       |
| datanasc      | varchar(20)  | YES  |     | NULL    |       |
| Especiality   | varchar(100) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

Answer 1

Your query does a cross join between the hours and doctors and then looks for hours not in the list of hours. Of course it doesn't return anything.

I think yo want something like this:

select DoctorHour.*
from (select d.Name, a.hour
      from Doctor d cross join
           (select distinct hour from Appointment) a
     ) DoctorHour left outer join
     Appointment a
     on a.name = DoctorHour.name and
        a.hour = DoctorHour.hour
where a.name is null;

This will get you all the unused slots.

Answer 2

Aside from the correctness (or incorrectness) of your query, you have a few errors.

You refer to one of the tables agenda_hours and Agenda_Hours and that might be causing an error. According to the MySQL docs:

Although database and table names are not case sensitive on some platforms, you should not refer to a given database or table using different cases within the same statement. The following statement would not work because it refers to a table both as my_table and as MY_TABLE:

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

Also, your field appoitment_hour seems to have a typo. It should probably be appointment_hour. So you're probably getting an "Unknown column" error.