CODEWITHSUNDEEP
mysqlsql
Mian Waqas
Mian Waqas

Reputation: 25

Query to get 3 variations of City Name

SELECT * 
FROM orders
WHERE date_ord >=  '2015-02-16'
AND date_ord <=  '2015-02-26'
AND active =1
AND (
(
ship_city =  'NYC'
OR ship_city =  'New York'
OR ship_city =  'New York City'
)
AND ship_state =  'NY'
)
OR (
(
bill_city =  'NYC'
OR bill_city =  'New York'
OR bill_city =  'New York City'
)
AND ship_state =  ''
)
ORDER BY ordId

Customers have added "New York" city name in 3 variations like "NYC", "New York" and "New York City". I need to query all those records where billing city is all 3 variations and shipping state is empty or NULL and those records where shipping city is new york using all 3 variations of city name and shipping state is set to 'NY'. In short, all orders which are shipped to New York City. (Billing state can be CA or FL)

This query is not perfect. I am getting records older than date range. Please suggest fix for this.

As suggested by @Ash and @DovesandChicks i tried to do it using IN function 

SELECT * 
FROM orders
WHERE date_ord >=  '2015-02-16'
AND date_ord <=  '2015-02-26'
AND active =1
AND ship_city
IN ('NYC',  'New York',  'New York City')
OR (bill_city IN ('NYC',  'New York',  'New York City') AND ship_state =  'Unlisted')
ORDER BY ordId

Still something wrong with query. Please help

Upvotes: 0

Views: 254

Answers (2)

DovesandChicks
DovesandChicks

Reputation: 388

1) you can use
AND ship_city IN ('NYC', 'New York', 'New York City') 2) You don't have enough brackets round your OR clauses. You need to have all the ORs in one bracket

so

WHERE (date bit) AND (city bit with ORs)

I can write it all out if you need me to:

    SELECT * FROM tblorders 
WHERE dateord >= '2015-02-16' 
AND dateord <= '2015-02-26' 
AND activeFlag =1 
AND (s_city IN ( 'NYC', 'New York', 'New York City' ) OR ( b_city IN ( 'NYC', 'New York', 'New York City' ) AND s_state = 'Unlisted' )) 
ORDER BY ordId

Upvotes: 1

ASh
ASh

Reputation: 35693

looks like you have unnecessary brackets near OR

SELECT * 
FROM orders
WHERE date_ord >=  '2015-02-16'
AND date_ord <=  '2015-02-26'
AND active =1
AND (
        (
            ship_city =  'NYC'
            OR ship_city =  'New York'
            OR ship_city =  'New York City'
        )
        AND ship_state =  'NY'

    --)
        OR 
    --(

        (
            bill_city =  'NYC'
            OR bill_city =  'New York'
            OR bill_city =  'New York City'
        )
        AND ship_state =  ''
    )
ORDER BY ordId

with proper formatting the query is more readable

condition can simplified in this way:

SELECT * 
FROM orders
WHERE date_ord >=  '2015-02-16'
AND date_ord <=  '2015-02-26'
AND active =1
AND ship_city in  ('NYC','New York','New York City')
AND ship_state in ('NY','')
ORDER BY ordId

Upvotes: 1

Related Questions