SQL query issue (MySQL)

user_777505 0
Viewed 73

My tables are:

Customers:

Phone  |  City
-------------------
1234   |  Chicago
2345   |  New York
1234   |  Toronto

City:

City      |  Country
--------------------
Chicago   |  USA
New York  |  USA
Las Vegas |  USA
Toronto   |  Canada
Montreal  |  Canada

User query: Phone = 1234, City = New York

If a phone number (1234) is searched in a city (New York). Even though there is no phone number (1234) that exists in the searched city (New York), But if that phone number (1234) exists in any city (Chicago) of the same country (USA) as of the searched city (New York), I want to fetch the details from Customers table because that phone number (1234) exists in any other city (Chicago) of the country (USA) which is the same country of the searched city (New York).

So, if user searches for phone = 1234, city = New York:

Result: Phone = 1234, city = Chicago

Unfortunately, user can not enter country name. He can only input phone and city

Update: Sorry, I forgot to mention that city name is primary key in City table. I purposely did it for the sake of simplicity. If there are 2 cities in 2 countries with same name, I only added the bigger one and did not add smaller one.

mysql sql
4 Answers

Test this:

SELECT t1.Phone, t2.City /* , t2.Country */
FROM Customers t1
JOIN City t2 USING (City)
JOIN City t3 USING (Country)
WHERE t1.Phone = 1234
  AND t3.City = 'New York'

Of course the query may produce more than one row. You may add ORDER BY t2.City = 'New York' DESC for the row with specified city to be the most upper (if exists).

user_101387340

Maybe this can be done with the following query:

SELECT * FROM customer WHERE Phone = 1234 AND City IN (SELECT City FROM city WHERE Country = (SELECT Country FROM city WHERE City = 'New York'));

Just replace the Phone and City in the query with the ones you want to search for.

user_11188780

You could use this query

SELECT phone, city
FROM Customers
WHERE phone = 1234   
    AND city IN 
    (
        SELECT city 
        FROM City
        WHERE country IN 
            (
                SELECT country 
                FROM city
                WHERE city = 'New York'
            )
    );
user_38444580

A bit late to the party but here's another solution. This creates a rank of matches and picks off the highest ranked match; you could easily adapt this to return additional matches based on matchability or similar criteria etc

See Fiddle

select c.phone, c.city
from customers c
where c.phone='1234'
order by 
case when exists (
        select * from cities ct where ct.city='new york'
            and ct.country=(select country from cities x where x.city=c.city)
        )
    then 1 else 0 end desc
limit 1
user_153326500
Related