JOIN returns all the data from the table

Question

I'm having a problem with my Select command, first of all, I need to show all the hours that are different from the idHour on other table here's the code:

SELECT DISTINCT Hours 
FROM agenda_hours A 
JOIN appoitment_hour B 
    ON (A.idHours <> B.Hour);

Result:

+----------+
| Hours    |
+----------+
| 07:00    |
| 07:30    |
| 08:00    |
| 08:30    |
| 09:00    |
| 09:30    |
| 10:00    |
| 10:30    |
| 11:00    |
| 11:30    |
| 12:00    |
| 12:30    |
| 13:00    |
| 13:30    |
| 14:00    |
| 14:30    |
| 15:00    |
| 15:30    |
| 16:00    |
| 16:30    |
| 17:00    |
| 17:30    |
| 18:00    |
| 18:30    |
| 19:00    |
| 19:30    |
| 20:00    |
+----------+

Why did you tag this with `MySQL` **and** `SQL Server` – Aug 24 '13 at 22:02 — , Aug 24 '13 at 22:02

score 2 · Answer 1 · answered Aug 24 '13 at 22:02

2

Try this way:

SELECT DISTINCT Hours 
FROM agenda_hours A 
LEFT JOIN appoitment_hour B 
    ON (A.idHours = B.Hour)
WHERE B.Hour is null;

answered Aug 24 '13 at 22:02

Robert

25,425
8
67
81

but no one of the idHours is null, this is the primary key of the table – Vinicius Aug 24 '13 at 22:07
I check `Hour` not `idHours` – Robert Aug 24 '13 at 22:08
1

@Vinicius if you look at this [SO question/answer](http://stackoverflow.com/questions/9770366/difference-in-mysql-join-vs-left-join) you will see that on a `LEFT JOIN` the joined value may be null if the value exists in the first table but not the second table. So this would return all of the hours that are in the 'agenda_hours' table that are not in the 'appoi[n]tment_hour' table. Which I also believe is what you want. +1 – amaster Aug 24 '13 at 23:12

score 0 · Answer 2 · answered Aug 24 '13 at 22:07

0

What you want is something like (is Hours from agenda_hours?)

SELECT DISTINCT Hours
FROM agenda_hours
WHERE idHours NOT IN (SELECT DISTINCT Hour FROM appoitment_hour)

You are having problems cause the JOIN tries to match all the rows in a table with all the rows in the other given the ON condition. So even if an hour is already in the other table, that row will match with all the others

answered Aug 24 '13 at 22:07

Filipe Roxo

622
3
10

2

If `appoitment_hour.Hour` allows `NULL`s then `NOT IN` should be avoid. Example: `SELECT 'Test' WHERE 1 NOT IN (2,3,NULL);` – Bogdan Sahlean Aug 24 '13 at 22:15

score 0 · Answer 3 · answered Aug 24 '13 at 22:11

Depending on which table includes Hours column you could use this query:

SELECT  /*DISTINCT*/ Hours 
FROM    agenda_hours A 
WHERE   NOT EXISTS(SELECT * FROM appoitment_hour B WHERE A.idHours = B.Hour);

or this one:

SELECT  /*DISTINCT*/ Hours 
FROM    appoitment_hour B
WHERE   NOT EXISTS(SELECT * FROM agenda_hours A WHERE A.idHours = B.Hour);

score 0 · Answer 4 · answered Aug 25 '13 at 03:31

0

For fetching non matching records

SELECT b.Hour, a.idHours FROM appoitment_hour b
LEFT JOIN agenda_hours a ON b.Hour = a.idHours
GROUP BY  b.Hour HAVING a.idHours IS NULL

answered Aug 25 '13 at 03:31

Shrikant Mavlankar

1,145
1
8
17

JOIN returns all the data from the table

4 Answers4