CODEWITHSUNDEEP
phpmysql
heyred
heyred

Reputation: 2051

Select * from table where date = this week and?

I have a cross platform enterprise app built using Monaca/Onsen UI and AngularJS that allows users to submit weekly reports on machinery they use during week. The checks include various safety checks e.g. current hours on machine, safety gear present etc.

This part of the app is working perfect and users can submit their weekly reports via dedicated tablets and smart phones.

Weekly report data is sent to a SQL database and I have a CMS built on the Sximo Builder using the Laravel framework and hosted on AWS. Admin users use the submitted report data to view reports and to ensure all reports for all machinery is submitted each week.

I need a query where admin users can quickly filter by all machinery that have NO reports submitted for any given week number.

But as my table only contains submitted reports, how do I check which machinery has not been submitted?

My table for the submitted reports looks as below with some dummy data.

Columns: ID, NAME, MACHINE ID, HOURS, SUBMITTED, DATE, WEEK NO with values 175, Jane Doe, H93, 2600, True, 03/05/2017 15:36, 21

I also have a machinery table that looks as below, that just holds the machinery details.

Columns: ID, MACHINE ID, MAKE, MODEL with values 1, H93, Ricoh, H1254

How do I display the list of machinery to admin users for any given week where reports have NOT been submitted.

Upvotes: 0

Views: 86

Answers (1)

LukStorms
LukStorms

Reputation: 29647

You could left join the report table to the table with the machinery.
And join on the machine id and the week number.
If there's no match, the report id will be null.

For example:

Some test data:

drop table if exists machineryreport;
create table machineryreport (ID int, NAME varchar(30), `MACHINE ID` varchar(4), HOURS int, SUBMITTED BOOLEAN, `DATE` date, `WEEK NO` int);
insert into machineryreport (ID, NAME, `MACHINE ID`, HOURS, SUBMITTED, `DATE`, `WEEK NO`) values
(175, 'John Doe', 'H92', 2600, True, '2017-03-05 15:36', 21),
(176, 'Jane Doe', 'H93', 2700, True, '2017-03-05 15:38', 21),
(177, 'Jake Doe', 'H95', 2900, True, '2017-03-06 15:42', 21);

drop table if exists machinery;
create table machinery (ID int, `MACHINE ID` varchar(4), MAKE varchar(30), MODEL varchar(8));
insert into machinery (ID, `MACHINE ID`, MAKE, MODEL) values
(1,'H92','Alderado','H1254'),
(2,'H93','Ricoh','H1254'),
(3,'H94','Consuela','H1254'),
(4,'H95','Josep','H1254');

Using a LEFT JOIN:

select m.* 
from machinery m
left join machineryreport r 
  on (m.`MACHINE ID` = r.`MACHINE ID` and
      r.`WEEK NO` = 21)
where r.ID is null;

Or use a NOT IN on a subquery:

select * 
from machinery 
where `MACHINE ID` not in (
    select distinct `MACHINE ID` 
    from machineryreport 
    where `WEEK NO` = 21
);

Or use a NOT EXISTS:

select * 
from machinery m
where not exists (
    select 1
    from machineryreport r
    where m.`MACHINE ID` = r.`MACHINE ID` 
      and r.`WEEK NO` = 21
);

Returns:

ID MACHINE ID MAKE     MODEL
-- ---------- ----     -----
3  H94        Consuela H1254

Upvotes: 1

Related Questions