Subquery in BETWEEN clause

Question

0.00/5 (No votes)

See more:

Hi every one
I want to use subquery in between clause but I'm getting error in SELECT.

SQL

SELECT TagNumber,ParkingType,DistanceToNextTag
FROM MyCar
WHERE Id BETWEEN (SELECT Id FROM MyCar WHERE TagNumber=6554) AND (SELECT Id FROM MyCar WHERE TagNumber=65635)

Posted 29-Sep-13 0:06am

Behno0o0oD

Add a Solution

3 solutions

Solution 1

Is there a chance that there are multiple Id values against one TagNumber?

You might wanna try

SQL

Top 1

in your

SQL

BETWEEN

SubQueries.

SQL

SELECT TagNumber,ParkingType,DistanceToNextTag
FROM MyCar
WHERE Id BETWEEN (SELECT top 1 Id FROM MyCar WHERE TagNumber=6554) AND (SELECT top 1 Id FROM MyCar WHERE TagNumber=65635)

Or add another condition in sub queries so that they return only one record.

Hope it helps.

Good luck

Azee...

Posted 29-Sep-13 0:21am

Azee

Comments

Behno0o0oD 29-Sep-13 6:42am

No, there is not, tag number is like primary key.

it does not work!

Azee 29-Sep-13 7:08am

What error are you getting?

Behno0o0oD 29-Sep-13 7:12am

SELECT top 1 Id FROM MyCar WHERE TagNumber=6554

This query is incorrect, I think.

Azee 29-Sep-13 7:15am

You wrote you are getting any error in Select, what is that specific error.
Post you MyCar table structure.

Solution 2

Without knowing the exact error message, it's quite impossible to say what the problem is.

However, if the TagNumber is unique, you could try an alternate version just in case. One point is that you haven't defined the tables for your columns in the query and older versions may have problems with scalars in between.

So try something like:

SQL

SELECT mc.TagNumber,
       mc.ParkingType,
       mc.DistanceToNextTag
FROM MyCar mc
WHERE mc.Id >= (SELECT mc2.Id FROM MyCar mc2 WHERE mc2.TagNumber=6554) 
AND   mc.Id <= (SELECT mc2.Id FROM MyCar mc2 WHERE mc2.TagNumber=65635)

Posted 29-Sep-13 1:41am

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Raja Sekhar S · Accepted Answer · 2013-09-29T21:49:00

If you want to use SubQuery u can use

SQL

SELECT TagNumber,ParkingType,DistanceToNextTag
FROM MyCar
WHERE Id in (Select Distinct Id From MyCar Where TagNumber BETWEEN 6554 AND 65635)

If you want to use a join...

SQL

SELECT mcid.TagNumber,mcid.ParkingType,mcid.DistanceToNextTag
FROM MyCar mcid
Inner join MyCar mctg on mcid.Id = mctg.Id
WHERE mctg.TagNumber BETWEEN 6554 AND 65635