mysql query
-
Hi guys, need help again.. newbie here just learning mysql. I have a timesheet table that have a few columns. ie Date, InvNum, Fieldhrs, UserID, ShopHrs. Everyday employees enter their data into database via web page which works great. My problem is this, trying to write a query that checks if every user has entered data for a certain date.. i've tried the following query, but just returns blank not userid ? SELECT UserID FROM timesheet WHERE "2018-04-23" NOT IN ( SELECT Date FROM timesheet ) Any help would be appreciated if someone could point me in right direction.. ie a real newbie.
-
Hi guys, need help again.. newbie here just learning mysql. I have a timesheet table that have a few columns. ie Date, InvNum, Fieldhrs, UserID, ShopHrs. Everyday employees enter their data into database via web page which works great. My problem is this, trying to write a query that checks if every user has entered data for a certain date.. i've tried the following query, but just returns blank not userid ? SELECT UserID FROM timesheet WHERE "2018-04-23" NOT IN ( SELECT Date FROM timesheet ) Any help would be appreciated if someone could point me in right direction.. ie a real newbie.
You need a reference for all active users. Assuming you have a Users table you should try something similar to this:
SELECT u.UserID
FROM Users u
LEFT JOIN Timesheet t
ON t.UserID = u.UserID
AND t.Date = "2018-04-23"
WHERE t.UserID IS NULL
AND u.IsActive = 1The existence of IsActive is just an assumption on my part. Add or change conditions as needed. There are other solutions, but this is the probably the best way to do it.
Wrong is evil and must be defeated. - Jeff Ello
-
You need a reference for all active users. Assuming you have a Users table you should try something similar to this:
SELECT u.UserID
FROM Users u
LEFT JOIN Timesheet t
ON t.UserID = u.UserID
AND t.Date = "2018-04-23"
WHERE t.UserID IS NULL
AND u.IsActive = 1The existence of IsActive is just an assumption on my part. Add or change conditions as needed. There are other solutions, but this is the probably the best way to do it.
Wrong is evil and must be defeated. - Jeff Ello
Thanks. Very helpful. Got it working following your suggestion. Much appreciated.
-
Thanks. Very helpful. Got it working following your suggestion. Much appreciated.
You're welcome
Wrong is evil and must be defeated. - Jeff Ello