John John - 24 days ago 7
MySQL Question

SQL - Return data from two differnt tables

I have two tables BOOKINGS and WORKERS. Basically there is table for a worker, and a table to keep track of what a worker has to do in a time frame aka (BOOKINGS). For example Jeff belongs to the WORKERS table, and in the BOOKING table Jeff is working from 2016-11-10 to 2016-11-15.

I’m trying to check if there is an available worker for a job. Say I need something done from 2016-11-11 to 2016-11-14. I need to be able to see if there is a worker who is not booked at that time, and display all the workers that are available for that job.
So I query the booking to check if requested time has available workers between the start end date.

However, the way I'm doing it does a join of the tables WORKERS and BOOKINGS and it only returns matching and non matching data out of BOOKINGS. For example say i have 10 workers and 4 workers workers are book at random times. My query only checks the 4 workers in BOOKINGS and disregards all the WOEKERS not yet with jobs. As you can see that is a huge problem. How do i query all the available workers as described ? Do i have to do the logic via my php script ?

Thanks everyone!!!!!! I understand this is a complex task and apperaite the time you took readings this.

Worker Table

CREATE TABLE WORKERS (
ID INT NOT NULL AUTO_INCREMENT,
WORKER_NAME VARCHAR(80) NOT NULL,
WORKER_CODE INT,
WORKER_WAGE INT,
PRIMARY KEY (ID)
)


Booking Table

CREATE TABLE BOOKINGS (
ID INT NOT NULL AUTO_INCREMENT,
WORKER_NAME VARCHAR(80) NOT NULL,
START DATE NOT NULL,
END DATE NOT NULL,
CLIENT_NAME VARCHAR(80) NOT NULL,
PRIMARY KEY (ID)
)


Client Table (Though not important)

CREATE TABLE CLIENTS (
ID INT NOT NULL AUTO_INCREMENT,
CLIENT_NAME VARCHAR(80) NOT NULL,
ADDRESS VARCHAR(100) NOT NULL,
PRIMARY KEY (ID)
)


Current query

SELECT WORKERS.ID, WORKERS.WORKER_NAME, WORKERS.WORKER_CODE, WORKERS.WORKER_WAGE
FROM WORKERS
INNER JOIN BOOKINGS
ON WORKERS.WORKER_NAME = BOOKINGS.WORKER_NAME
WHERE (START NOT BETWEEN :start AND :end)
ORDER BY WORKERS.ID


PHP Statement

$conn = new PDO("mysql:host=$servername;dbname=$bname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT WORKERS.ID, WORKERS.WORKER_NAME, WORKERS.WORKER_CODE, WORKERS.WORKER_WAGE
FROM WORKERS
INNER JOIN BOOKINGS
ON WORKERS.WORKER_NAME = BOOKINGS.WORKER_NAME
WHERE (START NOT BETWEEN :start AND :end)
ORDER BY WORKERS.ID
");
$stmt->bindParam(':start', $start);
$stmt->bindParam(':end', $end);
$stmt->execute();
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
$workers = $stmt->fetchAll();


Examples
First

Second

Third

Answer

You can select all available workers with:

SELECT * 
FROM WORKERS AS w
WHERE NOT EXISTS ( 
    SELECT 1
    FROM BOOKINGS 
    WHERE START BETWEEN :start AND :end
      AND WORKER_NAME = w.WORKER_NAME
)