CODEWITHSUNDEEP
mysql
Sikawai Perisai
Sikawai Perisai

Reputation: 1

View Data Per Day MySQL

I'm making a simple application about the daily assessment test of employees in the office.

The application stores the score results of several daily questions in the database, daily questions are inputted every day.

I have 2 tables in my MySQL Database, there is:

  1. account_table
  2. assessment_table

This is account_table :

accountID | accountName | accountEmail  
        1 | John        | [email protected] 
        2 | Derick      | [email protected]
        3 | Ratna       | [email protected]

And this is assessment_table: (for example today is 2020-10-08)

     assessmentID  |  accountID    |  assessmentScore | assessmentDate
      1            |   1           |  50              | 2020-10-07
      2            |   2           |  75              | 2020-10-07
      3            |   3           |  93              | 2020-10-07
      4            |   2           |  47              | 2020-10-08
      5            |   3           |  80              | 2020-10-08

How can I see accounts that haven't done an assessment today?
Thank you for your help, Regards.

Upvotes: 0

Views: 159

Answers (7)

vidur punj
vidur punj

Reputation: 5871

By using the below command if some extra entries present in the assessment_table which do not have an entry in the account table will be rejected.

SELECT * FROM account_table 
WHERE accountID NOT IN 
(SELECT assessment_table.accountID FROM assessment_table 
 RIGHT JOIN account_table on account_table.accountID = assessment_table.accountID 
 WHERE assessment_table.assessmentDate = DATE(NOW()))

Upvotes: 0

Hasnat Babur
Hasnat Babur

Reputation: 342

SELECT * FROM account_table WHERE account_table.accountID NOT IN (
    SELECT accounts.accountID
    FROM assessment_table assessments
    INNER JOIN account_table accounts ON assessments.accountID = accounts.accountID
    WHERE assessments.assessmentDate = "2020-10-08"
)

Upvotes: 1

Akina
Akina

Reputation: 42632

SELECT act.*
FROM account_table act
LEFT JOIN assessment_table ast ON act.accountID = ast.accountID 
                              AND ast.assessmentDate = '2020-10-08'
WHERE ast.accountID IS NULL

or

SELECT *
FROM account_table act
WHERE NOT EXISTS ( SELECT NULL 
                   FROM assessment_table ast 
                   WHERE act.accountID = ast.accountID 
                     AND ast.assessmentDate = '2020-10-08' )

You may use CURRENT_DATE instead of '2020-10-08' literal for to receive the output for current day always.

PS. I do not show WHERE NOT IN variant - it is slower in almost all cases.

Upvotes: 3

Mark
Mark

Reputation: 1068

I think the simplest way would be something like this:

SELECT * FROM account_table 
WHERE accountID NOT IN (SELECT accountID FROM assessment_table WHERE assessmentDate = DATE(NOW()));

Upvotes: 0

Juan Pablo Pisano
Juan Pablo Pisano

Reputation: 127

Just

SELECT ac_t.* FROM `account_table` ac_t
INNER JOIN `assessment_table` as_t
ON ac_t.accountID = as_t.accountID
WHERE as_t.accountID NOT IN (
    SELECT accountID FROM assessment_table WHERE `assessmentDate` = '2020-10-08'
    )

If you want today not hardcoded

SELECT ac_t.* FROM `account_table` ac_t
INNER JOIN `assessment_table` as_t
ON ac_t.accountID = as_t.accountID
WHERE as_t.accountID NOT IN (
    SELECT accountID FROM assessment_table WHERE `assessmentDate` = CURDATE()
    )

Upvotes: 0

sepei
sepei

Reputation: 1

This sql statement should work

SELECT account_table.accountID 
FROM account_table 
WHERE account_table.accountID NOT IN
(SELECT assessment_table.accountID 
FROM assessment_table 
WHERE CAST(assessmentDate AS DATE) = CAST("2020-10-08" AS DATE))

"2020-10-08" you could use functions like NOW() to get all entries of today

Upvotes: 0

Waqar Akram
Waqar Akram

Reputation: 117

hi Please use this sql statement

  $today = date("d");
    $month = date('m');
    $year  = date('Y');
    $query = "SELECT * FROM account_table as account inner join assessment_table as on account.accountID=assesment.accountId  where DAY(assesment.assessmentDate) = $today AND MONTH(assesment.assessmentDate) = $month AND YEAR(assesment.assessmentDate) = $year";

Upvotes: -2

Related Questions