Available appointments

I am building an appointments app…the user will be able to see a list of available appointments(timeslots) and choose.

For that I have made a table of possible appointments that covers a period of 7 months…it has a date column and a time column…to every day some time slots are allocated(09:00:00 to 22:00:00).

There is another table also that holds the appointments booked…

So if I want to find the available appointments i must join the 2 above tables…

How to do it though?
How I can grab specific appointments from the avail_apps table EXCLUDING the time-slot for which there is an appointment already.

How I can perform such a join

If you use a LEFT JOIN, you will get result rows regardless of whether there is a match or not. So you can check for a NULL value to see where there is not a match.

For example, this will show all records where there is NOT a corresponding record on table 2

SELECT t1.fieldA
     , t1.FieldB
     , t2.FieldC
     , T2.FieldD
  FROM table1 t1
  LEFT JOIN table2 t2 ON t1.id = t2.t1_id
 WHERE t2.FieldC IS NULL

LEFT JOIN with IS NULL filter – correct

however, the IS NULL condition should be on the join column – in the example shown, t2.FieldC might actually be legitimately null on an existing joined row

oops. You’re right. was trying to move too quick,

there is one slight problem though…that complicates things.

The format in the columns in table A is not the same with the format in the columns table B.

This is table A…where the possible apps list is stored:
CRE ATE TABLE avail_apps( idint(11) NOT NULL AUTO_INCREMENT, date_avdate NOT NULL, time_av time DEFAULT NULL, PRIMARY KEY (id,date_av) ) ENGINE=InnoDB AUTO_INCREMENT=8192 DEFAULT CHARSET=utf8

And here is table B…I am going to show only columns of interest:

CREATE TABLE appointments(
startDate datetime NOT NULL,
endDate datetime NOT NULL,

) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8`

So there is a dicrepancy between them…this later table holds the formats in like this:2015-11-17
18:30:00

I chose this format cause that is the format the app accepts that I am building.

What can I do?

I have fixed the issue with the formats…the query that does what I want is this:

SELECT date_av,time_av FROM avail_apps LEFT JOIN appointments ON avail_apps.date_av = appointments.startDate and avail_apps.time_av=appointments.startime WHERE startDate IS NULL and startime IS NULL;

It “catches” the available appointments slots.
There is one more detail left.

The appointments table is this:
CREATE TABLE appointments( apIDint(11) unsigned NOT NULL AUTO_INCREMENT, bookedformediumint(11) unsigned DEFAULT NULL ', startDatedate NOT NULL, startimetime NOT NULL, endDatedate NOT NULL, endTime time NOT NULL, PRIMARY KEY (apID`),

KEY bokkedfor (bookedfor),

) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8`

I have ommited some columns that are of no interest here.
There is a column named bookedfor…this represents the business provider for whom the app is booked.

What I want is just to find the available appointments for a specific provider…this col holds intergers…4 for example.

So I would like to find the available apps for provider 4…
Here this is a case if combining an inner join with a left join,how to do it?

add this –

AND bookedfor = 4

to the ON clause of the left join

thanks…

there is one more thing…that complicates thing a little more.
Another criteria must be added to.

Currently I am getting that available timeslots based on:

  • the already booked appointments

  • and for a specific provider
    A third one must be added and has to do with the schedule of each provider(the hours of the day the store is open)…I keep these in this table:

CREATE TABLE store_open( idint(11) NOT NULL AUTO_INCREMENT, b_user_IDmediumint(11) unsigned DEFAULT NULL, open_timetime DEFAULT NULL, close_timetime DEFAULT NULL, open_time_btime DEFAULT NULL, close_time_btime DEFAULT NULL, dayvarchar(12) DEFAULT NULL, model_ID int(11) DEFAULT NULL, PRIMARY KEY (id), KEY b_user_ID (b_user_ID), CONSTRAINT busers_reference FOREIGN KEY (b_user_ID) REFERENCES business_users (crID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

Where is says _b it refers to the second hour zone where a store is open…
for example some stores close at noon and open again at afternoon…I just call that b zone.

How could possible bring into play this 3rd criteria…of course I am not sure the current table design(above) can help in that or maybe I must redesign this table in order to accomplish the greater goal

Any ideas…
Thanks

[quote=“designtrooper, post:9, topic:207497, full:true”]
…of course I am not sure the current table design(above) can help in that or maybe I must redesign this table in order to accomplish the greater goal
Any ideas…[/quote]

my initial thought is that you have a very clumsy structure here

what if some provider decides to have morning, afternoon, and evening sessions

then you’ll need a “c time”

with the increased complexity of sql that would result

my idea is to create one simple table that covers ~all~ avaliable time slots

Can you be more specific…I already have one such table(as being evident by this topic).
Do you mean creating a second one just for the reason of storing the schedule(the sessions) of providers?

no, a single table to handle all available slots

As I said there is already such table…the problem now is integrating to it the provider sessions which are depicted in another table…its structure shown in post 9.

what If I changed the structure of the table where I store the schedule…
Something like this:
open_a_monday,close_a_monday,open_b_monday,close_b_monday,open_a_tuesday,close_a_tuesday,…

nope, what if you needed to add a “c” time… :smile:

the table needs two data columns: open time and close time

the rest of the table should all be key columns

Are you implying to create another table altogether?

Where am I going to store other data if there are only two data columns…where am I going to store the days(open,close hours must correspond to the weekdays) and the provider ID

no, just fix the ones you have :slight_smile:

[quote=“designtrooper, post:16, topic:207497, full:true”]Where am I going to store other data if there are only two data columns…where am I going to store the days(open,close hours must correspond to the weekdays) and the provider ID
[/quote]
in the key columns

can you give me an example for the structure of this table…difficult to understand without an example.

Are the key columns going to be 7 for example…one for each weekday?

here you go

bear in mind, please, that i do not know what b_user_ID and model_ID mean

CREATE TABLE store_open ( b_user_ID MEDIUMINT(11) UNSIGNED NOT NULL , day_open VARCHAR NOT NULL , open_time TIME NOT NULL , close_time TIME NOT NULL , model_ID INTEGER NOT NULL , PRIMARY KEY ( b_user_ID , day_open , open_time ) ); INSERT INTO store_open VALUES ( 3 , 'Monday' , '09:00:00' , '12:00:00' , 444 ) ,( 3 , 'Monday' , '13:00:00' , '17:00:00' , 444 ) ,( 3 , 'Monday' , '18:00:00' , '21:00:00' , 444 ) ,( 6 , 'Friday' , '09:00:00' , '18:00:00' , 444 ) ,( 8 , 'Tuesday', '09:00:00' , '12:00:00' , 444 ) ;

see? not only “b” times but “c” times as well

I see…some comments.
B_user_ID refers to the provider…it is a foreign key and it references a table where data about the provider are stored(name etc…)…model_ID refers to a backbone model.

Apart from that though I am not sure if this structure serves our purpose.

Getting the available appointments…by adding the criteria of hours and days the store is open(which this table stores)…if you look in the topic further up we have successfully added the criteria of the already booked appointments.

For example and for provider 3(as seen in your example) his/her available appointments cannot start from 08:00 as the store opens at 09:00:00

Are you getting me?