CODEWITHSUNDEEP
sqlsqlite
nameless_noob
nameless_noob

Reputation: 29

Join only top (latest) values for each rown in other table

I am new to SQL, so existing answers a bit complicated to me. I have three tables:

WORKER
|id 
|name
|date
|...

JOB
|id
|name
|salary
|accept

APPOINTMENT
|id
|worker_id
|job_id
|date

So if worker was appointed several times in a year I need to know what work he had at some specified time.

I have something like this now:

SELECT w.name,w.id FROM worker w 
INNER JOIN appointment a ON w.id = worker_id
INNER JOIN job j on job_id = j.id 
WHERE accept = 1 AND a.date <= (SELECT date FROM orders WHERE id = 2);

Now it shows all appointments less or equal than some date, but I need only last one for each worker. How I need to modify it?

EDIT:

ORDER
|id
|accepted_by //worker_id
|...

Orders is used to just get date. It can be changed to any from any source. So it isn't important in this context. Accept in Job is just a bool value, it represents that appointed worker can accept new orders. So the full meaning for this is to show in edit form in ComboBox all workers that was able to accept an order (not just workers that can accept it now), when the order was created.

Date is represented as integer value of days from 1970. Lines that are pretend to be in output:

w.name  w.id  a.id  a.date  j.name        j.accept
Smith   2     7     42999   administrator 1
Joe     1     6     42994   administrator 1
Smith   2     5     42994   waiter        0
Joe     1     4     42993   waiter        0
Smith   2     3     42992   administrator 1
Smith   2     2     42991   waiter        0
Smith   2     1     42990   administrator 1

What I receive with my query (it is listed above this edit) a.date <= 42998 and accept = 1;

Joe     1     6     42994   administrator 1
Smith   2     3     42992   administrator 1 //isn't current Smith's job
Smith   2     1     42990   administrator 1 //isn't current Smith's job

What I should to receive with my query a.date <= 42998;

//Last job in 42998
Joe     1     6     42994   administrator 1
Smith   2     5     42994   waiter        0

What I should to receive with my query a.date <= 42999;

//Last job in 42999
Smith   2     7     42999   administrator 1
Joe     1     6     42994   administrator 1

What I need to receive finally (a.date <= 42998 and accept=1):

//Workers which were able to accept order in 42998
Joe     1     6     42994   administrator 1

What I should to receive if (a.date <= 42999 and accept=1) ;

//Workers which were able to accept order in 42999
Smith   2     7     42999   administrator 1
Joe     1     6     42994   administrator 1

Tables (all unused fields are removed):

CREATE TABLE appointment (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, worker_id INTEGER NOT NULL, job_id INTEGER, date INTEGER NOT NULL);
CREATE TABLE worker (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name TEXT NOT NULL);
CREATE TABLE job (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name TEXT NOT NULL,accept INTEGER NOT NULL);

Inserts (like in example above):

INSERT INTO worker (name) VALUES ('Joe');
INSERT INTO worker (name) VALUES ('Smith');
INSERT INTO job (name,accept) VALUES ('waiter',0);
INSERT INTO job (name,accept) VALUES ('administrator',1);
INSERT INTO appointment (worker_id,job_id,date) VALUES (2,2,42990);
INSERT INTO appointment (worker_id,job_id,date) VALUES (2,1,42991);
INSERT INTO appointment (worker_id,job_id,date) VALUES (2,2,42992);
INSERT INTO appointment (worker_id,job_id,date) VALUES (1,1,42993);
INSERT INTO appointment (worker_id,job_id,date) VALUES (2,1,42994);
INSERT INTO appointment (worker_id,job_id,date) VALUES (1,2,42994);
INSERT INTO appointment (worker_id,job_id,date) VALUES (2,2,42999);

Upvotes: 1

Views: 37

Answers (1)

CL.
CL.

Reputation: 180210

To get the last appointment for each worker, use grouping. Filter out the non-accepted ones in a second step:

SELECT ...
FROM (SELECT worker_id,
             job_id,
             MAX(date) AS date
      FROM appointment
      WHERE date <= ...
      GROUP BY worker_id) AS a
JOIN worker AS w ON a.worker_id = w.id
JOIN job    AS j ON a.job_id    = j.id
WHERE accept = 1;

Upvotes: 1

Related Questions