Right join problem

Databases
1

I have a table that lists possible date and times(avail_apps) in a 7 month span(half hour intervals) and a second table that lists the schedule()store_open( of a hair salon(for example)

By joining these two(most probably it is a right join) I want to get the possible timeslots this hair salon is open.

Everything you want to see about these tables you will find it here http://sqlfiddle.com/#!9/bda98/2

I am not sure that the store_open table the way is currently designed can help us achieve what I am describing here…that is…getting the possible timeslots

P.S bear in mind that the table where the date times is stored(avail_aps) is the result of a cross join between the date and time tables…also depicted in the link I gave

2

So… your app has no processing power of it’s own? You have to do EVERYTHING in the database engine?

3

you want an INNER JOIN because you want actual matching timeslots, not timeslots that don’t match open hours or open hours that don’t match timeslots

SELECT store_open.b_user_ID , store_open.open_time , store_open.close_time , store_open.`day` , avail_apps.date_av , avail_apps.time_av FROM avail_apps INNER JOIN store_open ON store_open.`day` = DAYNAME(avail_apps.date_av) AND store_open.open_time BETWEEN avail_apps.time_av AND avail_apps.time_av + INTERVAL 30 MINUTE

i did not bother to run this against your fiddle, because it will not return anything with the data provided (easy to confirm by visual inspection)

4

this isn’t the complete solution, because it won’t show all available slots, just the first

but it should get you started

5

just saw your post…give me some time to examine it.

6

It does not quite work as expected…for instance it show me as available time slot in Tuesday that of Monday’s.

Despite it’s overall logic is correct some corrections must be made before continuing making the LEFT JOIN.

P.S let me know if it is better to prepare an example…cause it might be better to work with actual data.

7

it is frustrating…

8

i thought you had actual data in your fiddle?

just make it more robust, i.e. create some slots that actually should be returned

9

The tables(store_open and avail_apps) are full with data in the fiddle…nonetheless when running your query I only get 2 rows- I do not know why-having said that I cannot show you exactly what is wrong your query…

You can see for yourself http://sqlfiddle.com/#!9/63fbf/7

Locally I get much more rows…

10

which 2 rows do you get?

because by visual inspection alone, only this row should be returned –

INSERT INTO store_open (`b_user_ID`, open_time, close_time, open_time_b, close_time_b, `day`, `model_ID`) VALUES (4, '10:00:00', '12:30:00', NULL, NULL, 'tuesday', 8);

11

first of all the store_open table in the fiddle holds all rows now…7 in total,for each weekday.

And if I run your query I get these 2:

b_user_ID open_time close_time day date_av time_av 4 January, 01 1970 10:00:00 January, 01 1970 12:30:00 tuesday November, 17 2015 00:00:00 January, 01 1970 10:00:00 4 January, 01 1970 10:00:00 January, 01 1970 12:30:00 tuesday November, 24 2015 00:00:00 January, 01 1970 10:00:00

It does not make sense.

Just run it yourself…

12

for store 4, which is open from 10:00 to 12:30 on tuesdays, there’s an available slot at 10:00 on tues nov 17

for store 4, which is open from 10:00 to 12:30 on tuesdays, there’s an available slot at 10:00 on tues nov 24

helps?

13

yes I understand…but where are the rest of the rows…

For example…where is row for December 1st and December 8th…also Tuesdays?

14

come on, you can do this … :smile:

CALL filldates('2015-11-13','2015-11-29');
15

Why are you showing me this…the procedures are already called…the avail_apps table has already data in it.

P.S GIVE ME A MINUTE PLEASE…

16

let me spell it out… the query cannot find any slots for december because you only filled the table for those dates in november

17

YES,YES AND YES…you are absolutely right…my fault I had not seen that earlier…

18

I cannot show you what is the problem with your query in fiddle due to the way the results are produced….same day over and over again.

Instead I have an image snapshot from my editor’s console where the problem is shown clearer.
http://1drv.ms/1TgmTZL

Take a look at the 2nd(tuesday) row on column time_av .
It says 09:30…this is the close_time of Monday…has nothing to do with Tuesday.

You will find similar errors(more or less) in other rows…

19

should i assume that you haven’t touched my query at all?

20

Why do you say that?
Did you look at the image snapshot?