2

I am trying to display customers who live in the same city as William. This query gets his zip code and finds any customers with his zip code. I need to find the customers in the city NAME based from the zip code. There are a few zip codes per city Name.

Table city: 

zipCode (PK) cityName, stateCode

Table customer: 

customerId (PK) customerName, customerAddress, zipCode (FK)

My query so far...

SELECT 
    cu2.customerName AS 'Customer Name', 
    ci2.cityName AS 'City Name'
FROM 
    customer as cu
INNER JOIN 
    city as ci ON cu.zipCode = ci.zipCode
INNER JOIN 
    city as ci2 ON ci.cityName = ci2.cityName
INNER JOIN 
    customer as cu2 ON ci2.zipCode = cu2.zipCode
                    AND cu2.customerName <> cu.customerName
WHERE 
    cu2.customerName = 'William'

It shows no results because I think it is only checking for William's zip code when the city he is in has two other zip codes. His is 91709 and the cityNAME has in addition 91708 and 91710. I need to get the other customers within that cityNAME.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
DDDD
  • 3,790
  • 5
  • 33
  • 55
  • Hi DDDD, take a look at my answer. This should work for you. Thanks, Mike – Mike91 Nov 25 '12 at 22:21
  • You may want to have a look at the accepted answer to [this](http://stackoverflow.com/questions/13112899/how-to-get-the-bounding-coordinates-for-a-us-postalzip-code) question for too much information about ZIP codes. City isn't really a concept with respect to ZIP codes, but may be adequate in your application. – HABO Nov 26 '12 at 00:42

3 Answers3

1

I think this does it.

SELECT
  allCustomer.customerName,
  allCity.cityName
FROM Customer finder
  JOIN City finderCity
   ON finder.zipCode = finderCity.ZipCode
  JOIN City allCity
    ON finderCity.CityName = allCity.CityName
    AND finderCity.StateCode = allCity.StateCode
  JOIN Customer allCustomer
    ON allCity.ZipCode = allCustomer.ZipCode
WHERE finder.customerName = 'William'
  and allCustomer.customerName != 'William'

Some notes:

  • If customerName is not unique, then we need to better identify William.
  • Only specify join criteria in the ON clause... do not put filtering criteria there.
  • A City (as a concept) is represented by multiple rows in the City table, which causes confusion.
Amy B
  • 108,202
  • 21
  • 135
  • 185
1

This SQL will do it (I have tried it myself) on mock tables:

SELECT customer.customerName, city.cityName FROM customer INNER JOIN city on city.zipCode = customer.zipCode WHERE city.cityName = (

SELECT city.cityName FROM city WHERE zipCode = (
SELECT city.zipCode FROM customer
INNER JOIN city ON customer.zipCode = city.zipCode
WHERE customer.customerName = 'William'
)

)
Mike91
  • 520
  • 2
  • 9
  • 25
  • I tried it using the same tables and attributes as you stated. Make sure that your city table is called city and your customer table is called customer and it should work. Also make sure that your column names are the same – Mike91 Nov 25 '12 at 22:29
  • I marked the other as the answer, this one does not show cityName – DDDD Nov 25 '12 at 22:39
  • No problem, although I did not see any requirement to show cityName. I have amended mine above to show this however... – Mike91 Nov 25 '12 at 22:44
0
Select cityName,customerName from customer
Join City on Customer.zipCode = City.ZipCode
Where 
And customer.customerName = 'William'
Group by cityName, customerName
lenago
  • 49
  • 5
  • There is no city name in customer. The city name is only in the city table. IN the Where it is only getting the zipcode. There are other zipCodes in the city. Didnt read my question. – DDDD Nov 25 '12 at 22:08