SQL does not produce what I want

Gus Tanudji shared this idea 2 months ago
Completed

I did this, which basically groups two different queries in brackets an AND operator:

SELECT * FROM Monthly_Merchant_Fees where (Year >= 2020 and Month >= 2) and (Year <= 2020 and Month <= 2)


But what I get is the following. Where are the brackets that I purposely add to groups two queries together?

db.Monthly_Merchant_Fees.find({

Year: {

$gte: 2019,

$lte: 2020

},

Month: {

$gte: 2,

$lte: 2

}

})

Comments (4)

photo
1

Actually,

I managed to activate "Verbose Shell", and the result is really weird. As you can see below, it does not have any reference to the any part of "$gte" query.

mb.runSQLQuery(`

SELECT * FROM Monthly_Merchant_Fees where (Year >= 2019 and Month >= 3) and (Year <= 2020 and Month <= 2)

`).sort({_id:-1})
.limit(100)

to the translated code:

/6237a95c31e2cd2cbf2acc56c104fe32

photo
1

We have worked out a new test build to resolve this "group and" issue, please download and give it a try.

MacOS: https://nosqlbooster.com/s3/download/releasesv5/nosqlbooster4mongo-5.2.11-beta.1.dmg

Windows: https://nosqlbooster.com/s3/download/releasesv5/nosqlbooster4mongo-5.2.11-beta.1.exe

photo
2

We have worked out a new test build to resolve this "group and" issue, please download and give it a try.

MacOS: https://nosqlbooster.com/s3/download/releasesv5/nosqlbooster4mongo-5.2.11-beta.1.dmg

Windows: https://nosqlbooster.com/s3/download/releasesv5/nosqlbooster4mongo-5.2.11-beta.1.exe

photo
1

Hi Qinghai,

I have installed the exe and it produces a different code now, which seems more correct.


However, I actually expect a different result as I explain below:

I have a collection, XYZ that contains:


Year Month Value 
2019 1          AA 
2019 2          AB 
2020 1          A 
2020 2          B 
2020 3          C
What I want to do is something like this in SQL, which basically making two groups of "and" queries

SELECT * FROM XYZ WHERE (Year >= 2019 and Month >= 3) and (Year <= 2020 and Month <= 2)

I expect the result to be

- 2020 1 A
- 2020 2 B

photo
1

There is something wrong with your query and the result is empty.

https://www.db-fiddle.com/f/fQEfNA9pnp8u6V1jME7xdG/0

photo
1

Thanks qinghai. I think your fix is good anyway.

photo
1

Resolved in 5.2.11

photo