Grab data in a single query

Question

I've got 3 tables which store the following values:

Users:
user_id
email
password
status

Scheduled_Jobs:
jobs_id
jobs_userid
date
status

Last_Update:
update_id
update_jobsid
status

When a user is created there is also a list of scheduled jobs created on a monthly bases (so for example 1 job/month based on the date registered). When a user finishes a job the Last_Update table is getting updated.

What I'm currently doing is trying to see how many users are up-to-date and have no pending jobs.

I'm currently going through a loop checking out which users have a status = 1 then their jobs status is also 1 and date is latest (date <= curdate(), so I can grab the latest job) and finally if there is an update in that table for that jobid.

I'm wondering if is it possible to get the same results but with just a single mysql query.

Update (an example):

Users (user_id | email | password | status)
1 | asd@asd.com  | xxx | 1
2 | asd2@asd.com | xxx | 1
3 | asd3@asd.com | xxx | 2
4 | asd4@asd.com | xxx | 1
5 | asd3@asd.com | xxx | 1

Scheduled_Jobs (job_id | user_id | date | status)
1  | 1 | 05/09/2019 | 1
2  | 1 | 05/10/2019 | 1
3  | 1 | 05/11/2019 | 1
4  | 1 | 05/12/2019 | 1
5  | 2 | 07/10/2019 | 1
6  | 2 | 07/11/2019 | 1
7  | 2 | 07/12/2019 | 1
8  | 3 | 10/10/2019 | 2
9  | 4 | 13/10/2019 | 1
10 | 4 | 13/11/2019 | 1
11 | 5 | 15/10/2019 | 1
12 | 5 | 15/11/2019 | 1
13 | 5 | 15/12/2019 | 1

Last_Update (update_id | job_id | status)
1 | 1  | 1
2 | 2  | 1
3 | 3  | 1
4 | 5  | 1
5 | 8  | 1
6 | 9  | 1
7 | 11 | 1

Based on the example above we've got 4 active users with scheduled jobs. The result will be (based on today) that 3 out of those 4 users have no pending jobs. As you can see userid 2 have missed a job (jobid = 6).

Possible duplicate of [SELECT \* FROM multiple tables. MySQL](https://stackoverflow.com/questions/12890071/select-from-multiple-tables-mysql) — Jorge C.M, Nov 08 '19 at 10:31
what do you want to get exactly? Which users do you want to fetch? Could you explain it as a normal sentence? For exmample: "I want the users that have all their possible status from their Last_Update.status (from their jobs) marked as `1`." — Chemaclass, Nov 08 '19 at 10:33
Welcome to SO. Please see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Nov 08 '19 at 10:34
Sample data and expected output will really help to frame the right query. I was trying to prepare a fiddle but stuck with questions. — verisimilitude, Nov 08 '19 at 10:49
@verisimilitude I've updated the question. Hope that is better now. Thank you! — Laon, Nov 08 '19 at 10:52
And what kind of output do you want? something like `user_id | pending_job (true/false)` ? — Pepper, Nov 08 '19 at 10:55
@Chemaclass For users 1 = active, 2 = inactive. For jobs 1 = active, 2 = removed. For Update 1 = active, 2 = removed. So I can keep track if something is removed (I basically keep it hidden) — Laon, Nov 08 '19 at 10:56
@Pepper Bascailly the number of users with no pending jobs at the current time. Thank you! — Laon, Nov 08 '19 at 10:56
Then I think selecting `COUNT(u.user_id)` instead of `u.*` in the query of my answer below should give you that number — Pepper, Nov 08 '19 at 10:58
Now is better. But it's still not good enough. Why not just follow the advice provided at the linked answer. — Strawberry, Nov 08 '19 at 10:59

Pepper · Accepted Answer · 2019-11-08T11:12:02.213

0

Edit: Based on your comments and example, I updated the query to return the number of Users that have no unfinished job (as in, Scheduled_Jobs with status = 1, that already passed their deadline but still have no Last_Update)

SELECT count(u.user_id) AS nb_users
FROM Users u
LEFT JOIN (
  SELECT sj.jobs_userid AS unfinished_user
  FROM Scheduled_Jobs sj
  LEFT JOIN Last_Update lu
    ON lu.update_jobsid = sj.jobs_id
  WHERE sj.status = 1
  AND sj.date <= CURDATE()
  AND lu.update_id IS NULL
) AS uj -- unfinished jobs
  ON u.user_id = uj.unfinished_user
WHERE u.status = 1
AND uj.unfinished_user IS NULL

edited Nov 08 '19 at 11:12

answered Nov 08 '19 at 10:43

Pepper

587
4
12

Thanks for the help. I'm not sure I understand the Jobs part. The scheduled jobs already got predefined dates which are supposed to be met. – Laon Nov 08 '19 at 11:10
@Laon I updated the answer, I'm still not certain I correctly understand the `date is latest (date <= curdate(), so I can grab the latest job)` part of your question, but from what I understand it seems you want people with no unmet job deadlines? – Pepper Nov 08 '19 at 11:15
Yes! I didn't see the updated answer. That works great. Thanks! – Laon Nov 08 '19 at 11:31

Grab data in a single query

1 Answers1