SQL nested select fixed

Question

I have two tables, hours and days, hours store the hours a profesional has available in a day. Days, store the the reserved hours,

table_hours

id  | begin   | end    | profesional 
1   | 09:00   |  10:00 | 1 
2   | 10:00   |  11:00 | 1 
3   | 11:00   |  12:00 | 1 
4   | 13:00   |  14:00 | 1 
5   | 14:00   |  15:00 | 1 
6   | 09:30   |  10:30 | 2 
7   | 13:00   |  14:30 | 2 
8   | 14:30   |  15:30 | 2

table_days

id  | hour_id | day
1  | 1        |20151201 
2  | 3        |20151201 
3  | 6        |20151201 
4  | 2        |20151205 
5  | 7        |20151205 
6  | 8        |20151205

I, tried the following query, but do not worked.

SELECT *, (SELECT day from days where hours.id = days.hours_id and day = '20151201') as 'day' 
FROM hours 
where profesional = 1

As result I would like to have all results from profesional and a date just when match as bellow

table_result

id  | begin | end    | profesional | day
1   | 09:00 |  10:00 | 1           |20151201
2   | 10:00 |  11:00 | 1           |NULL
3   | 11:00 |  12:00 | 1           |20151201
4   | 13:00 |  14:00 | 1           |NULL
5   | 14:00 |  15:00 | 1           |NULL

@PraveenKumar Did that really make the tables easier to read? — Barmar
– Barmar, Commented Dec 28, 2015 at 23:12

Barmar · Accepted Answer · 2015-12-29 00:53:17Z

3

Use LEFT JOIN to get all the rows in the first table, and return NULL in the columns from the second table when there's no matching row.

SELECT h.*, d.day
FROM hours AS h
LEFT JOIN days AS d ON h.id = d.hours_id AND d.day = '20151201'
WHERE h.profesional = 1

edited Dec 29, 2015 at 0:53

answered Dec 28, 2015 at 23:17

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 1 · Accepted Answer · 2015-12-29 02:07:14Z

1

SELECT table_hours.id, table_hours.begin, table_hours.end, table_hours.professional, table_day.day  
FROM table_day LEFT JOIN table_hours ON  table_day.id=table_hours.id AND table_day.day= '20151201'
Where day= professional=1

edited Dec 29, 2015 at 2:07

answered Dec 28, 2015 at 23:38

user2404781

5 Comments

Barmar Over a year ago

INNER JOIN won't show NULL for rows that have no match.

user2404781 Over a year ago

Edited, Thanks.One more thing that i am not sure of, In my WHERE clause do i need to mention the name of the tables like table_day.day='20151201' or it works as it is?

Barmar Over a year ago

Only if the column names are ambiguous.

Barmar Over a year ago

When you use LEFT JOIN, you need to put the day test in the ON clause. Otherwise it will won't show rows where day = NULL.

Barmar Over a year ago

I also like using the table names to make it clear to readers, even if the name isn't ambiguous. But I also like assigning short table name aliases.

Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ · Accepted Answer · 2015-12-29 02:23:49Z

I think, you have already got the solution here from other answers. But I just wanted to point out another thing, you have set your database in a wrong way. Instead of keeping hour_id in days table, keep day_id in hours table. It should reduce rows in your database and help you save storage in the long run. So it can be like this:

table_hours

id  | day_id  | begin   | end    | profesional 
1   | 1       | 09:00   |  10:00 | 1 
2   | 2       | 10:00   |  11:00 | 1 
3   | 1       | 11:00   |  12:00 | 1 
4   | 3       | 13:00   |  14:00 | 1 
5   | 3       | 14:00   |  15:00 | 1 
6   | 1       | 09:30   |  10:30 | 2 
7   | 2       | 13:00   |  14:30 | 2 
8   | 2       | 14:30   |  15:30 | 2

table_days

id  | day
1   | 20151201 
2   | 20151205

And now change your SQL like this

SELECT h.*, d.day
FROM hours AS h
LEFT JOIN days AS d ON h.day_id = d.id AND d.day = '20151201'
WHERE h.profesional = 1

Collectives™ on Stack Overflow

SQL nested select fixed

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related