Help with JOIN logic...

Arrrgggg, nothing makes any sence any more. I need to write a select statement that will display results based on one of two things...

Zip Code OR City State

By the way this is a Table of addresses with zip codes, I also have a Lat/Long table associated to all the different zip codes that need to be joined.

The Psuedo code is somethinglike this...

SHOW All People WITH Appropreate Longditude and Latitude
Who Live
In @.This ZipCode
OR
(In @.State AND @.City)

------
Table Dealers
------
dlrID - varchar
dlrName - varchar
dlrAddress - varchar
dlrCity - varchar
dlrState - varchar
dlrZip - varchar
dlrCountry - varchar
------

------
Table ZipCodes
------
zipCode
zipCity
zipState
zipCountrty
zipLat
zipLong
...
------

I have achieved this with Joins but the query takes 2+ minutes to execute

What am I doing wrong? I am sure there is a better approach to this!

Thanks,
--LitoOption A: Would it be worth attempting a UNION query to achieve this?

SHOW all people in this ZIP Code

UNION

SHOW all people in this City and State.

Option B: Write a stored Procedure:

CREATE PROC spShowDealers (
@.ZipCode varchar(10) = '',
@.City varchar(30) = '',
@.State char(2) = ''

AS

IF ZipCode = ''
BEGIN
SHOW all dealers where City = @.City and State = @.State
END

ELSE
SHOW all dealers where ZipCode = @.ZipCode
END|||Thanks for the suggestion,

the problem with UNION is that I will end up with duplicates, because the zip code could be valid as well as the city and state info.

Stored Procedure might work,

something like
if zip code doesn't return a record,
try city and state combination.

Maybe :)

I'll give it a shot, thanks again.

--Lito|||You won't end up with duplicates using UNION. You will end up with duplicates if you use UNION ALL. Big difference here. Unfortunately, there's also a big difference in performance.|||You won't end up with duplicates using UNION. You will end up with duplicates if you use UNION ALL. Big difference here. Unfortunately, there's also a big difference in performance.

I do end up with duplicates, and I am not sure what to do about it.

here is the query...
SELECT DISTINCT(UTIL_DLR_ONLY.DealerID), UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State, ZCUG.Long, ZCUG.Lat
FROM ZCUG, UTIL_DLR_ONLY WHERE UTIL_DLR_ONLY.Zip = ZCUG.ZIP

UNION
SELECT DISTINCT(UTIL_DLR_ONLY.DealerID), UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State, ZCUG.Long, ZCUG.Lat
FROM ZCUG, UTIL_DLR_ONLY WHERE UTIL_DLR_ONLY.City = ZCUG.City AND UTIL_DLR_ONLY.State = ZCUG.St

And here are the results...
...

Mesa AZ 111.788 33.3970 C014900
Mesa AZ 111.789 33.3976 C014900
Mesa AZ 111.804 33.4484 C014900
Mesa AZ 111.842 33.3901 C014900
Mesa AZ 111.847 33.4411 C014900
Mesa AZ 111.848 33.4340 C014900
Mesa AZ 111.873 33.3827 C014900
Mesa AZ 111.875 33.3845 C014925
Phoenix AZ 111.964 33.6787 C014925
Phoenix AZ 111.973 33.4505 C014925
Phoenix AZ 111.981 33.4998 C014925
Phoenix AZ 111.981 33.5003 C014925
Phoenix AZ 111.987 33.4647 C014925
Phoenix AZ 111.987 33.4650 C014925
Phoenix AZ 111.987 33.4651 C014925
Phoenix AZ 111.987 33.4653 C014925

...

I know it's because of many->one relatonship between LONG and LAT and the City/State, but what can I do about it?

Thanks,
Lito|||I'm not seeing the duplicate rows. There are multiple rows for the same city, but they have different coordinates (probably for the postal center).

Are you looking to get only one row returned by city and state? If so, just group on the city and state columns.

-PatP|||Pat Phelan,

That's what I mean when I get duplicates is that I only need one city and state to be listed...

I have tried GROUP BY and I get an error...
Column 'ZCUG.Long' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I include 'ZCUG.Long' in the group clause I get the same result...

This thing should not be this difficult ...

Does any one know a better way of doing this?|||If you don't want duplicates on Long/Lat, then you need to exclude those columns from the SELECT.|||What you appear to want is:SELECT
UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON (UTIL_DLR_ONLY.Zip = ZCUG.ZIP )
UNION SELECT
UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON (UTIL_DLR_ONLY.City = ZCUG.City
AND UTIL_DLR_ONLY.State = ZCUG.St)Give that a try and see if it does what you want.

-PatP|||If you don't want duplicates on Long/Lat, then you need to exclude those columns from the SELECT.

The problem is I need Long/Lat, but only one set per City/State .|||The problem is I need Long/Lat, but only one set per City/State .Oh, you should have said that up front! Then I'd use:SELECT City, State, Min(Long), Min(Lat)
FROM (
SELECT
UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State, ZCUG.Long, ZCUG.Lat
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON (UTIL_DLR_ONLY.Zip = ZCUG.ZIP )
UNION SELECT
UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State, ZCUG.Long, ZCUG.Lat
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON (UTIL_DLR_ONLY.City = ZCUG.City
AND UTIL_DLR_ONLY.State = ZCUG.St)) AS a
GROUP BY 1, 2-PatP|||I really appreciate you helping me with this, it is driving me nuts!

PatP,
This produces almost exactly what I want, except I also need, DealerID and the (Long, Lat) cordiantes , and as soon as I add those to the query, everything breaks, because of the many to one relationship of long,lat and the city/state combo, is there a way to specify, only the first set of long/lat per city?

Thanks.

What you appear to want is:SELECT
UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON (UTIL_DLR_ONLY.Zip = ZCUG.ZIP )
UNION SELECT
UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON (UTIL_DLR_ONLY.City = ZCUG.City
AND UTIL_DLR_ONLY.State = ZCUG.St)Give that a try and see if it does what you want.

-PatP|||Try the revised query I posted using Min to see if that gets closer. If it does, in the case of a large city which dealership do you want to use (lowest or highest DealerID/Long/Lat)? How do you deal with ties if they are possible (same lat or long, etc)?

-PatP|||Try the revised query I posted using Min to see if that gets closer. If it does, in the case of a large city which dealership do you want to use (lowest or highest DealerID/Long/Lat)? How do you deal with ties if they are possible (same lat or long, etc)?

-PatP

Thank you so much
This is as good as it gets!!!

If there happen to be 2 dealers in the same Long/Lat, it doesn't matter which one, so long as we find one of them. I sort the list by DealerID, so I guess the lowest Dealer ID will get pulled. I made some changes to the revised query to accomodate my needs, but other then that This is unbeliveable, I have spen about 6 hours trying all sorts of combinations to produce this result and you solve it in mins.

Thanks again PatP.

--Lito

modified...
SELECT DealerID, City, State, Zip, Min(Long) 'Long', Min(Lat) 'Lat'
FROM (
SELECT
UTIL_DLR_ONLY.DealerID, UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State, UTIL_DLR_ONLY.Zip, ZCUG.Long, ZCUG.Lat
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON UTIL_DLR_ONLY.Zip = ZCUG.ZIP
WHERE UTIL_DLR_ONLY.Country = 'CA'
UNION SELECT
UTIL_DLR_ONLY.DealerID, UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State, UTIL_DLR_ONLY.Zip, ZCUG.Long, ZCUG.Lat
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON (UTIL_DLR_ONLY.City = ZCUG.City
AND UTIL_DLR_ONLY.State = ZCUG.St)
WHERE UTIL_DLR_ONLY.Country = 'CA')
AS a
GROUP BY DealerID,City, State, Zip
ORDER BY DealerID