SQL with multiple AND's

Codes here !

Moderators: egami, macek, gesf

Post Reply
cjkeane
New php-forum User
New php-forum User
Posts: 3
Joined: Sat Mar 28, 2020 9:50 am

Fri May 22, 2020 8:08 am

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
User avatar
hyper
php-forum GURU
php-forum GURU
Posts: 1190
Joined: Mon Feb 22, 2016 5:52 pm

Fri May 22, 2020 9:10 am

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?
Post Reply