SQL with multiple SELECT statement

Question

Hi I have a bit of a problem with my SQL. My problem is when I filter the dates of my data, my SQL statement only returns 2 records which was the employee_id of the user.

This is the original statement of my working code that I have in filtering dates:

SELECT * FROM `rmguidef_prs`.`payment_report` WHERE DATE(`Report_Timestamp`) = DATE_ADD(CURDATE(), INTERVAL 24 HOUR)  AND Employee_ID IN (
                               SELECT T1.Employee_ID from `rmguidef_prs`.`employee` T1
                               WHERE T1.Supervisor_ID = '".$id."')
                               OR Employee_ID = '".$id."'

This is the modified statement that I need the filters: (its working ok)

 SELECT * FROM `report` WHERE Employee_ID IN (
                               SELECT T1.Employee_ID from `employee` T1
                               WHERE T1.Supervisor_ID = '".$id."')
                               OR Employee_ID IN (
                               SELECT T2.Manager_ID from `branches` T2
                               WHERE T2.Manager_ID = '".$id."')
                               OR Employee_ID = '".$id."'

But when I add this: DATE(Timestamp) = DATE_ADD(CURDATE(), INTERVAL 24 HOUR) in the statement to filter the date the only records shown are coming from this: OR Employee_ID = '".$id."'

SELECT * FROM `report` WHERE DATE(`Timestamp`) = DATE_ADD(CURDATE(), INTERVAL 24 HOUR) AND Employee_ID IN (
                           SELECT T1.Employee_ID from `employee` T1
                           WHERE T1.Supervisor_ID = '".$id."')
                           OR Employee_ID IN (
                           SELECT T2.Manager_ID from `branches` T2
                           WHERE T2.Manager_ID = '".$id."')
                           OR Employee_ID = '".$id."'

I want my modified SQL statement to be filtered by today's date, week, month, and past 3 months. I already have to code for those filters but I cannot figure out how to add those to my statement.

Hart CO · Accepted Answer · 2014-11-16 05:47:18Z

1

Add parenthesis to group criteria:

SELECT * 
FROM `report` 
WHERE DATE(`Timestamp`) = DATE_ADD(CURDATE(), INTERVAL 24 HOUR) 
  AND (   Employee_ID IN (SELECT T1.Employee_ID 
                          FROM `employee` T1
                          WHERE T1.Supervisor_ID = '".$id."'
                          )
       OR Employee_ID IN (SELECT T2.Manager_ID 
                          FROM `branches` T2
                          WHERE T2.Manager_ID = '".$id."'
                          )
       OR Employee_ID = '".$id."'
      )

AND takes precedence over OR, so your date criteria was only being considered for the first of the three Employee_ID criteria.

Edit: After re-reading your question I'm not entirely sure if the above is what you're after, your date criteria would only return records with tomorrow's date, which probably doesn't exist in the Timestamp field, so that's probably the real problem.

edited Nov 16, 2014 at 5:47

answered Nov 16, 2014 at 5:37

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3771102 Over a year ago

Oh! Its okay because the time is aligned with the server time which is advanced by 1 day. I'll see if this works.

Collectives™ on Stack Overflow

SQL with multiple SELECT statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related