Sql query: date range and value greater than 10 each year-CodePudding

I have table which contain say 3 columns( companyname, years, roc). I want to find out companyname which satisfy below condition. Between years 2010 and 2020 and roc> 10 each years. Mean if in any years between 2010 and 2020 roc<10 it should not include that company. It should only show companyname if each year roc >10.

CodePudding user response：

SELECT companyname from TABLE_NAME WHERE years>=2010 AND years<=2020 AND roc>10

CodePudding user response：

Okay, I think just now I got your problem. What is the main issue in here is, the companies get duplicated within a single table which violates the DB principles what apparently makes you harder when querying back.

So the best thing you could do is, break this single table into two - COMPANY_TABLE and COMPANY_ROC

So the COMPANY_TABLE will only have the companyID(PRIMARY KEY) and the companyName.

Next have another table called COMPANY_ROC - which will have roc , years , companyID(FOREIGN KEY - PRIMARY KEY of COMPANY_TABLE).

CREATE TABLE COMPANY(
   companyID int PRIMARY KEY NOT NULL,
   companyName varchar,
)


CREATE TABLE COMPANY_ROC(
   companyID int,
   roc int,
   years int,
   FOREIGN KEY(companyID) REFERENCES COMPANY(companyID)
)

SO when you querying, you can query as follows using an INNER JOIN

SELECT COMPANY.companyName from COMPANY INNER JOIN COMPANY_ROC WHERE COMPANY_ROC.years>=2010 AND COMPANY_ROC.years<=2020 AND COMPANY_ROC.roc>10 AND COMPANY.companyID = COMPANY_ROC.companyID

Maybe my Query might have some issues as I didn't test it. Just understand what I explained and give it a try. Breaking the table into two and having primary , foreign keys is the key for easy querying :)