Error in SQL query using Where

Question

Im trying to make an query that shows the adresses with the ZIP of 1391, 1393, 1396, 1426, 1427 and all the zips between 3400 and 3433.

SELECT ZIP, HOUSE_NR, APPARTMENT_NR, VerzBedrag
FROM werkelijke_inboedels
WHERE ProductLine = 'inboedel'
AND Product_Option = 'Inboedel All Risk' OR 'Inboedel Extra Uitgebreid'
AND ZIP = %'1391'% OR %'1393'% OR %'1396'% OR %'1426'% OR %'1427'%
AND ZIP > %'3400'%
AND ZIP < %'4133'%

Can someone change the query in the right one?

why are you wildcarding the ZIP search?

mcalex
– mcalex

2013-02-28 17:32:01 +00:00
Commented Feb 28, 2013 at 17:32 — mcalex
– mcalex, Commented Feb 28, 2013 at 17:32

John Woo · Accepted Answer · 2013-02-28 17:32:09Z

4

You only need to use IN and BETWEEN to work the query as expected. Pattern matching is not needed here.

SELECT  *
FROM    tableName
WHERE   ProductLine = 'inboedel' AND 
        Product_Option IN ('Inboedel All Risk','Inboedel Extra Uitgebreid') AND
        (
            ZIP IN (1391, 1393, 1396, 1426, 1427) OR
            ZIP BETWEEN 3400 AND 3433
        )

edited Feb 28, 2013 at 17:32

answered Feb 28, 2013 at 17:26

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user330315 Over a year ago

The OP doesn't actually want a LIKE. A plain IN without the % should do just fine

John Woo Over a year ago

@a_horse_with_no_name yes you're right. It just needs IN and BETWEEN

warnerst Over a year ago

It does not work.. In the result I also get also ZIP codes bigger than 4133 and smaller than 3400

John Woo Over a year ago

have you tried my updated one. the first query i posted did not group the OR condition.

John Woo Over a year ago

you're welcome :D by the way, What RDBMS you are using? RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, etc...

|

mcalex · Accepted Answer · 2013-02-28 17:33:58Z

4

AND ZIP IN ('1391', '1393', '1396', '1426', '1427')
OR  ZIP BETWEEN  '3400' AND '4133'

edited Feb 28, 2013 at 17:33

answered Feb 28, 2013 at 17:27

mcalex

6,7985 gold badges53 silver badges86 bronze badges

3 Comments

mcalex Over a year ago

yeah. I can't see why they're needed anyway

Trent Over a year ago

running between on strings is risky business :) Avoid it like the plague.

mcalex Over a year ago

should be fine if they're all 4-'digit' zips

Kevin Seifert · Accepted Answer · 2013-02-28 17:27:23Z

0

wild cards go inside of quotes:

'%1391%'

These are used with the LIKE operator.

answered Feb 28, 2013 at 17:27

Kevin Seifert

3,5921 gold badge21 silver badges14 bronze badges

Collectives™ on Stack Overflow

Error in SQL query using Where

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related