I have a table (shown below) that ideally if I could would make the companyid and the start and end dates unique in such a way that one can input the same companyid so long as the start and end dates do not overlap each other. I haven’t found a way to make this possible so I am attempting to detect any possible user errors just to inform the user of invalid entries.
I’ve established several rules for this table, mainly :
- EntryDate < ExpDate
- Muiltiple ProspectCompanyID may not have intertwining EntryDate < ExpDate
Right now I’m just trying to see if I can detect these errors. Rule 1 was easy to find, but rule 2 is a tad bit more difficult.
Here is an example of my table:
CREATE TABLE IF NOT EXISTS `CompanyTitleHolder` (
`CompanyTitleHolderID` int(10) NOT NULL auto_increment,
`CompanyID` int(10) NOT NULL,
`MemberID` int(10) NOT NULL,
`EntryDate` date NOT NULL,
`ExpDate` date NOT NULL COMMENT 'Expiration Date',
PRIMARY KEY (`CompanyTitleHolderID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Ownership Table';
Here is my first query which finds all the records where more than one entry is entered per companyID
SELECT
`CompanyID`,
COUNT(`CompanyID`) as cnt
FROM`CompanyTitleHolder`
GROUP BY `ProspectCompaniesID`
HAVING cnt > 1
ORDER BY `ProspectCompaniesID`
My second query needs to iterate through each companyid from the first and manually check that dates are not over-lapping each other.
Pseudocode: iterate through each companyid from first query as $companyid
SELECT
*
FROM`CompanyTitleHolder`
where id = $companyid
pseudocode: look at each record and compare startdate2 is not between startdate1 and enddate2
As you can tell the second part isn’t very well fleshed out at this point. I was wondering if I could combine the two queries as I think* that querying a query is redundant if you do not plan to make many subqueries for a query… (perhaps I am wrong though)
Is there a way to take the start and end date from one row and compare them to other rows without splitting the query. and is it possible to iterate through each owner.
This is rather complex since I’m unable to express the idea very clearly so if you need more specification, don’t hesitate to ask
P.S. If there is a simplier method, I will take those suggestions too.