SQL with multiple AND's

[cjkeane]

Hi everyone,

I have been working on this SQL statement for quite a long time and I am not sure where the issue is. If you could point me in the right direction, I would appreciate it.

I can write it like this and it returns records between two dates. From this query, I have confirmed that there are records for the Dummy company name within those dates.

Code: Select all

SELECT records.CompanyName, negotiations.CaseNumber, negotiations.DateAgreement
FROM records
RIGHT JOIN negotiations ON records.CaseNumber = negotiations.CaseNumber
WHERE negotiations.DateAgreement
BETWEEN  '2000-01-01'
AND  '2020-01-01'
ORDER BY negotiations.CaseNumber DESC 

I can write it like this and return the records based on the company name, and it returns records:

Code: Select all

SELECT records.CompanyName, negotiations.CaseNumber, negotiations.DateAgreement
FROM records
RIGHT JOIN negotiations ON records.CaseNumber = negotiations.CaseNumber
WHERE records.CompanyName =  'Dummy'
ORDER BY negotiations.CaseNumber DESC 

But if I write it like this, it returns no records. I know there are records between those dates for the company name of Dummy. I need the query written this way so that I can retrieve records between those dates within the negotiations table and where the company name matches as well.

Code: Select all

SELECT records.CompanyName, negotiations.CaseNumber, negotiations.DateAgreement
FROM records
RIGHT JOIN negotiations ON records.CaseNumber = negotiations.CaseNumber
WHERE negotiations.DateAgreement
BETWEEN  '2000-01-01'
AND  '2020-01-01'
AND records.CompanyName =  'Dummy'
ORDER BY negotiations.CaseNumber DESC

[hyper]

I can't see anything wrong, but you could try it this way round:

Code: Select all

SELECT records.CompanyName, negotiations.CaseNumber, negotiations.DateAgreement
FROM records
RIGHT JOIN negotiations ON records.CaseNumber = negotiations.CaseNumber
WHERE records.CompanyName =  'Dummy'
AND negotiations.DateAgreement
BETWEEN  '2000-01-01'
AND  '2020-01-01'
ORDER BY negotiations.CaseNumber DESC

That said, I can't help but think there is something wrong if you have a case number and need to date check it, unless I'm missing something, a case number (unless duplicated which I would think is bad practice) would be unique and applied on the date the case was created?