Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. I lov C in AIX/Linux, hate C++
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jul 2003
    Location
    Jacksonville, Florida
    Posts
    1,655
    Rep Power
    34

    Within the INNER JOIN, how to limit the row to 1 row inside the INNER JOIN?


    There is a table called "tblvZipCodes" that contain a zipcode of all cities, area code that are located in that zip code.

    The problem I have with the inner join is that there are more than 1 cities in one zipcode code. Is there a way to just return only the 1st row and not return the rest of the rows from the tblvZipCodes in the INNER JOIN query?

    Thanks..

    Code:
    SELECT     TOP 100 PERCENT dbo.tblPurchaseRaw.Year, dbo.tblPurchaseRaw.Make, dbo.tblPurchaseRaw.Model, dbo.tblPurchaseRaw.ModelType, 
                          dbo.tblPurchaseRaw.Color, dbo.tblvZipCodes.ZIPCode, dbo.tblvZipCodes.City, dbo.tblvZipCodes.County, dbo.tblvZipCodes.State, 
                          dbo.tblvZipCodes.AreaCode, dbo.tblvZipCodes.Region, dbo.tblaAccounts.Name, dbo.tblaAccounts.PhoneOne, dbo.tblaAccounts.AccountID, 
                          dbo.tblPurchaseRaw.AcceptedID, dbo.tblPurchaseRaw.Series, dbo.tblPurchaseRaw.BodyStyle, dbo.tblaAccounts.WebSite, 
                          dbo.tblaAccounts.SalesEmail, dbo.tblPurchaseRaw.EmailTo, dbo.tblPurchaseRaw.PhotoURL, dbo.tblPurchaseRaw.Mileage, 
                          dbo.tblPurchaseRaw.RawID, dbo.tblvRegions.Name AS RegionName, dbo.tblPurchaseRaw.VIN, dbo.tblPurchaseRaw.Style, 
                          dbo.tblPurchaseRaw.StockDate
    FROM         dbo.tblPurchaseRaw INNER JOIN
                          dbo.tblaAccounts ON dbo.tblPurchaseRaw.AccountID = dbo.tblaAccounts.AccountID INNER JOIN
                          dbo.tblvZipCodes ON dbo.tblPurchaseRaw.ZipCode = dbo.tblvZipCodes.ZIPCode INNER JOIN
                          dbo.tblvRegions ON dbo.tblvZipCodes.Region = dbo.tblvRegions.RegionID
    WHERE     (CONVERT(char, dbo.tblPurchaseRaw.StockDate, 101) <> '01/01/1900') AND (dbo.tblPurchaseRaw.SoldRawID IS NULL) AND 
                          (dbo.tblPurchaseRaw.AcceptedID <> - 10) AND (dbo.tblPurchaseRaw.AcceptedID <> - 1)
    ORDER BY dbo.tblvZipCodes.ZIPCode
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    there is no such thing as "the first row" in a relational table

    you must specify some column value -- the city with the highest name, or the smallest number of letters, or something like that

    and isn't "TOP 100 PERCENT" kind of silly?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. I lov C in AIX/Linux, hate C++
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jul 2003
    Location
    Jacksonville, Florida
    Posts
    1,655
    Rep Power
    34
    I didn't do the "top 100 percent" query part, somebody or MSSQL did it.

    I need a way to limit the tblvZipCode to "top 1" row so we don't get the duplicate vehicles (tblPurchaseRaw). It did not occur to us that there are 3 cities with a same zip code.

    Thanks...
  6. #4
  7. ASP.Net MVP
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Aug 2003
    Location
    WI
    Posts
    4,378
    Rep Power
    1511
    3 ways I can think of right now:
    1) Use a subquery in the select clause instead of joining the table. If the subquery returns more than one row you'll get an error, so you'll have to limit the subquery to the first row (very easy, because you can just do a TOP 1).
    2) Have an additional condition on your join that effectively limits joined rows to the first record.
    3) Use a subquery in the FROM clause and GROUP BY your Zip.

    I think #3 would perform best, but imo is the hardest to write.
    Last edited by f'lar; April 17th, 2007 at 01:26 PM.
    Primary Forum: .Net Development
    Holy cow, I'm now an ASP.Net MVP!

    [Moving to ASP.Net] | [.Net Dos and Don't for VB6 Programmers]

    http://twitter.com/jcoehoorn
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    good answer f'lar

    i prefer #3 because it's the easiest to write
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. I lov C in AIX/Linux, hate C++
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jul 2003
    Location
    Jacksonville, Florida
    Posts
    1,655
    Rep Power
    34
    Yea, I have been looking at option #3 before I made the first posting. The view is made up of 4 tables.

    I haven't got it to work right just yet. I'm thinking of ways to make it possible.
  12. #7
  13. ASP.Net MVP
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Aug 2003
    Location
    WI
    Posts
    4,378
    Rep Power
    1511
    Originally Posted by fletchsod
    Yea, I have been looking at option #3 before I made the first posting. The view is made up of 4 tables.
    Speaking of views, a view to flatten out your data would be a 4th option.
    Primary Forum: .Net Development
    Holy cow, I'm now an ASP.Net MVP!

    [Moving to ASP.Net] | [.Net Dos and Don't for VB6 Programmers]

    http://twitter.com/jcoehoorn
  14. #8
  15. I lov C in AIX/Linux, hate C++
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jul 2003
    Location
    Jacksonville, Florida
    Posts
    1,655
    Rep Power
    34
    Stored Procedure would be an another option but I wouldn't recommended it because of 2 or more of different sql query would hurt the database performance.

    This is the best I got but I got 58 rows with a zipcode in several of the different states.

    Instead of the "min()", I tried the "distinct()" and "top 1" and they don't work. I welcome a better suggestion. I made a sub query in the "INNER JOIN" part. Whatever I did, it give me more than 50 rows.

    Code:
    SELECT     dbo.tblPurchaseRaw.Year, dbo.tblPurchaseRaw.Make, dbo.tblPurchaseRaw.Model,  
               dbo.tblPurchaseRaw.VIN, dbo.tblPurchaseRaw.StockDate, 
               dbo.tblvZipCodes.ZIPCode, dbo.tblvZipCodes.City, dbo.tblvZipCodes.State
    FROM         dbo.tblPurchaseRaw INNER JOIN
                          dbo.tblvZipCodes ON dbo.tblPurchaseRaw.ZipCode = (select min(tblvZipCodes.ZIPCode) from tblvZipCodes where tblvZipCodes.zipcode = tblPurchaseRaw.zipcode)
    WHERE     (Year = '2003') AND (Make = 'BMW') AND (Model = '5 Series') AND (RawID = '1169276')
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    so you have some zipcodes that correspond to multiple cities

    and you are joining vehicle purchase records to the zipcodes, and pulling out a city and state

    the good news is, yes, it is possible to write a query that returns only one city for each zipcode

    the bad news is, sometimes the data will be wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. I lov C in AIX/Linux, hate C++
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jul 2003
    Location
    Jacksonville, Florida
    Posts
    1,655
    Rep Power
    34
    Yea 1 zipcode that contain multiple cities. In this case, I'm going to have to grab just 1 city with a 2 in 3 chances that the city would be wrong. We don't have much option in this case.

    I did this sql query

    Code:
    select min(tblvZipCodes.ZIPCode) from tblvZipCodes where tblvZipCodes.zipcode = '08505'
    and I get 1 row and 1 city. Not bad. The INNER JOIN work a little differently than I thought. I'll have to think of something.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    how often will you be re-populating the zipcodes table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. I lov C in AIX/Linux, hate C++
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jul 2003
    Location
    Jacksonville, Florida
    Posts
    1,655
    Rep Power
    34
    Well, whenever the 2 or 3 web pages are being accessed.
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    no, i mean the data

    where did you get the zipcodes data from? it will get stale, right? and you are going to refresh it how?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. I lov C in AIX/Linux, hate C++
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jul 2003
    Location
    Jacksonville, Florida
    Posts
    1,655
    Rep Power
    34
    Well, it is static. If a new update to the zipcode table need to be updated then we have to get it from somewhere and re-update the table like once a year or something.

    Anyway, problem solved. A former employee came over to take a look at it, he added the identity to the zipcode table and add the sql query.

    Code:
    SELECT     dbo.tblPurchaseRaw.Year, dbo.tblPurchaseRaw.Make, dbo.tblPurchaseRaw.Model,  
               dbo.tblPurchaseRaw.VIN, dbo.tblPurchaseRaw.StockDate, 
               dbo.tblvZipCodes.ZIPCode, dbo.tblvZipCodes.City, dbo.tblvZipCodes.State
    FROM         dbo.tblPurchaseRaw INNER JOIN
                          dbo.tblvZipCodes ON dbo.tblPurchaseRaw.ZipCode = (SELECT     tblvZipCodes_2.ZIPID, tblvZipCodes_2.ZIPCode, tblvZipCodes_2.ZIPCodeType, tblvZipCodes_2.City, tblvZipCodes_2.CityType, 
                                                       tblvZipCodes_2.County, tblvZipCodes_2.CountyFIPS, tblvZipCodes_2.State, tblvZipCodes_2.StateCode, tblvZipCodes_2.StateFIPS, 
                                                       tblvZipCodes_2.MSA, tblvZipCodes_2.AreaCode, tblvZipCodes_2.TimeZone, tblvZipCodes_2.GMTOffset, tblvZipCodes_2.DST, 
                                                       tblvZipCodes_2.Latitude, tblvZipCodes_2.Longitude, tblvZipCodes_2.Region
                                FROM          dbo.tblvZipCodes AS tblvZipCodes_2 INNER JOIN
                                                           (SELECT     MIN(ZIPID) AS zipid
                                                             FROM          dbo.tblvZipCodes AS tblvZipCodes_1
                                                             GROUP BY ZIPCode) AS t ON tblvZipCodes_2.ZIPID = t.zipid)
    WHERE     (Year = '2003') AND (Make = 'BMW') AND (Model = '5 Series') AND (RawID = '1169276')
    Probably not as clear to understand them. OH well.
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    thanks for answering my question -- if the data is static, then i would find all zipcodes which correspond to multiple cities, and choose whichever one makes the most sense, so that when you're done, the entire table has just one row per zipcode

    and by choosing which city corresponds to a zipcode, that way you won't be wrong quite so often

    and the sql would be ~way~ more efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo