New Topic

NoSQLBooster for MongoDB (formerly MongoBooster)Topic Idea

SQL does not produce what I want

Gus Tanudji shared this idea 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

}

})

I like this idea 0

Replies (4)

Gus Tanudji ●

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

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

Reply URL

qinghai (support@nosqlbooster.com) ●

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

Reply URL

qinghai (support@nosqlbooster.com) ●

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

Reply URL

Gus Tanudji ●

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

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

Reply URL

qinghai (support@nosqlbooster.com) ●

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

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

URL

Gus Tanudji ●

Thanks qinghai. I think your fix is good anyway.

URL

qinghai (support@nosqlbooster.com) ●

Resolved in 5.2.11

URL