CODEWITHSUNDEEP
mysqlsqldatabase
Farrel Jones
Farrel Jones

Reputation: 13

How to query and compare values of two tables?

I need some input on my query. What I am attempting to do is compare a time entered by the user to a block of times stored in a database to check to see if that time slot is available.

The problem I am running into however is the user's value is in one table and the block of values in another table. My query is below. Any help would be appreciated. I have exhausted all my ideas!

EmpID = FOREIGN KEY

EXAMPLE:

User Enters - 2016-04-06 09-00-00 (of type date time in DB) I then run what I thought was this query below and see if there is a time block available between lets say 2016-04-06 09-00-00 AND 2016-04-06 10-00-00

I then update that time as being OK to be scheduled.

UPDATE TimeRequestedTable
SET AppointTime = "inputed by user"
WHERE EmpID IN 
      (SELECT BlockTimesTable.EmpID
       FROM BlockTimesTable
       WHERE
         (SELECT AppointTime
          FROM TimeRequestedTable)
       BETWEEN startTime and EndTime);

Upvotes: 1

Views: 33

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

Seems you need an inner join update 

UPDATE TimeRequestedTable
INNER JOIN BlockTimesTable ON BlockTimesTable.EmpID = TimeRequestedTable.EmpID
SET AppointTime = "inputed by user"
WHERE AppointTime BETWEEN startTime and EndTime

Upvotes: 1

Related Questions