Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Posts
    46

    Unanswered: 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,
    --Lito

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Option 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
    Have you hugged your backup today?

  3. #3
    Join Date
    Nov 2003
    Posts
    46

    Lightbulb

    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

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    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.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Nov 2003
    Posts
    46
    Quote Originally Posted by derrickleggett
    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...
    ...
    Code:
    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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  7. #7
    Join Date
    Nov 2003
    Posts
    46

    Red face

    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?

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If you don't want duplicates on Long/Lat, then you need to exclude those columns from the SELECT.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What you appear to want is:
    Code:
    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

  10. #10
    Join Date
    Nov 2003
    Posts
    46
    Quote Originally Posted by rdjabarov
    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 .

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by lito
    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:
    Code:
    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

  12. #12
    Join Date
    Nov 2003
    Posts
    46
    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.


    Quote Originally Posted by Pat Phelan
    What you appear to want is:
    Code:
    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

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  14. #14
    Join Date
    Nov 2003
    Posts
    46

    Talking

    Quote Originally Posted by Pat Phelan
    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...
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •