SQLite Forum

>= and <= (or between potentially) query
Login

>= and <= (or between potentially) query

(1) By Clive (G4NAQCDM) on 2022-01-24 14:03:47 [link] [source]

I have a table with 3 columns the first 2 columns (Prefix_Start and Prefix_End) of 3 characters and the 3rd (Country_Name) a string representing a country. 

The first 2 columns are start of range and end of range and contain values for a range e.g. A4A and A4Z are the two values. 

I have a query I wish to execute to find out which country relates to 3 characters I have input so A4A for example.. 

Select Country_Name FROM Country WHERE Prefix_Start >= "A4A" and Prefix_End < "A4A"

this query returns no data.. please can someone suggest where I am going wrong?

Thanks

(2) By Larry Brasfield (larrybr) on 2022-01-24 14:10:13 in reply to 1 [link] [source]

Your test selects for values within an empty range. The WHERE clause is false forever, assuming Prefix_Start <= Prefix_End (which seems like the only sensible way to do such a thing.)

(4) By Clive (G4NAQCDM) on 2022-01-24 14:13:11 in reply to 2 [link] [source]

Sorry Larry, Typo in Query the Prefix_End <= 'A4A' is what I actually have..

(3.1) By Ryan Smith (cuz) on 2022-01-24 14:13:26 edited from 3.0 in reply to 1 [link] [source]

Select Country_Name FROM Country WHERE Prefix_Start >= "A4A" and Prefix_End < "A4A"

Where you are going wrong is very easy: You are asking for values that are both equal-or-larger than A4A and also smaller than A4A, that set is empty and any other expectations is a physical impossibility in the known universe.

Perhaps you mean:

Select Country_Name FROM Country WHERE Prefix_Start >= 'A4A' and Prefix_End < 'A4Z'

Note also the single-quotes, which are the real string-quoting characters in SQL.

(5) By Clive (G4NAQCDM) on 2022-01-24 14:14:17 in reply to 3.0 [link] [source]

ThanksI get it!

(6) By Ryan Smith (cuz) on 2022-01-24 14:21:52 in reply to 5 [source]

Just out of interest, could you explain what is the purpose of prefix_start and prefix_end columns?

Should you not simply have a prefix column? Do they ever differ for the same record?

Do you mind posting the output of this query:

SELECT * FROM Country LIMIT 10;

It will give us great clarity and we might have a suggestion how you can improve usage and/or speed for your queries or use case.

(7.1) Originally by Clive (G4NAQCDM) with edits by Stephan Beal (stephan) on 2022-01-24 14:30:33 from 7.0 in reply to 6 [link] [source]

Still up the creek with this..

Data looks like this..
Prefix_Start Prefix_End  Country_Name
A2A 	     A2Z	Botswana (Republic of)
A3A	     A3Z	Tonga (Kingdom of)
A4A	     A4Z	Oman (Sultanate of)
A5A	     A5Z	Bhutan (Kingdom of)
A6A	     A6Z	United Arab Emirates
A7A	     A7Z	Qatar (State of)
A8A	     A8Z	Liberia (Republic of)
A9A	     A9Z	Bahrain (Kingdom of)

I have A4A as the input.... so I want to retrieve "Oman"..

(8) By Larry Brasfield (larrybr) on 2022-01-24 14:40:23 in reply to 7.1 [link] [source]

Please study this working code: create table if not exists Country(prefixBeg text, prefixLim text, name); insert into Country values ('A2A', 'A2Z', 'Botswana (Republic of)'), ('A3A', 'A3Z', 'Tonga (Kingdom of)'), ('A4A', 'A4Z', 'Oman (Sultanate of)'), ('A5A', 'A5Z', 'Bhutan (Kingdom of)'); .parameter set @prefix "'A4A'" select name from Country where @prefix>=prefixBeg and @prefix<prefixLim; , which runs in the SQLite shell and acts as I expect and you should too.

(9) By Clive (G4NAQCDM) on 2022-01-24 14:50:10 in reply to 8 [link] [source]

Brilliant :) my SQL skills need improving... Thanks Larry