Need SQL Guru, Question Enclosed

[SteveJ34]

I had someone pose a question to me regarding SQL syntax that I am not well versed enough to answer. I thought someone here may have a greater level of background in this area and might be able to suggest a solution:

Background:

Table 1: Name/Address/Zipcode
Table 2: Low Zip, High Zip Ranges

Data Example:

Table 1:
Steve Johnson, POB xxxx,...., 75052
Jay Dennis, 234 Brittany, ...., 76039

Table 2:
10010, 10030
75000, 76000

The question is how to accomplish a SQL select that extracts the records out of Table 1 that fall within the range of zips in Table 2.

Any clues or someone that might be able to help construct the SQL statement to accomplish?

I look forward to any and all replies.

[Jabba]

At first blush:

SELECT a.*
FROM TABLE1 a, TABLE2 b
WHERE a.zipcode >= b.zipcode_low and a.zipcode <= b.zipcode_high

should do it...This syntax is based on Oracle, but should be generic enough for what you are trying to do. I put it on separate lines for clarity, you should type all on one line in case SQL client does not accept lines broken up...

What database are you using?

[SirPoonga]

should do it...This syntax is based on Oracle, but should be generic enough for what you are trying to do. I put it on separate lines for clarity, you should type all on one line in case SQL client does not accept lines broken up...

SQL is SQL, n matter the database.  But yes, some databases have extra syntax.  However, being "broken up" is part of the SQL standard so if it says SQL compliant....


SteveJ, are you assuming 5 digit zipcodes with no alpha characters?

[SteveJ34]

Thanks guys.

By first glance it certainly looks simple enough. I've passed the info along and will report success. I assume by design it will loop through all the records in table 2/b comparing each range?

The database backend in this specific situation I believe is MS SQL.

And yes, the database is strictly US based with the zip range table being only 5 digits.

[Jabba]

Yeah it will join the two tables. Make sure your Hi Lo table does not have overlapping ranges. If they do, the record (address) may get selected rtwice...If you were adding up sales order, this could do double accounting. Now if it's calculating Sales commission and you are the sales guy, heh, leave it as is

[Jabba]

SQL is SQL, n matter the database.