Professional Web Applications Themes

Generate results based on another collection of results? (users and zip codes) - Microsoft SQL / MS SQL Server

I apologize for asking this, because I know I've seen it before but Google is letting me down. I have a query that gets a list of zip codes within a certain area. That works fine. Now how do I get records from another table (users) that have one of the zip codes from the first result set? If I get 30 zip codes, it would seem inefficient to query the user table 30 times, once for each zip code. Can you help? My SQL is rusty to say the least. Naturally this would be a stored procedure....

  1. #1

    Default Generate results based on another collection of results? (users and zip codes)

    I apologize for asking this, because I know I've seen it before but
    Google is letting me down.

    I have a query that gets a list of zip codes within a certain area.
    That works fine. Now how do I get records from another table (users)
    that have one of the zip codes from the first result set? If I get 30
    zip codes, it would seem inefficient to query the user table 30 times,
    once for each zip code.

    Can you help? My SQL is rusty to say the least. Naturally this would
    be a stored procedure.
    Jeff Guest

  2. #2

    Default Re: Generate results based on another collection of results? (users and zip codes)

    Please post your DDL with INSERT's of sample data. Perhaps the following will help:

    select
    *
    from
    MyTable m
    join
    (
    select
    Zip
    from
    ZipCodes
    where
    State = 'CA'
    and City = 'Sacramento'
    ) x on x.Zip = m.Zip

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Jeff 'Jones' Putz" <com> wrote in message news:google.com...
    I apologize for asking this, because I know I've seen it before but
    Google is letting me down.

    I have a query that gets a list of zip codes within a certain area.
    That works fine. Now how do I get records from another table (users)
    that have one of the zip codes from the first result set? If I get 30
    zip codes, it would seem inefficient to query the user table 30 times,
    once for each zip code.

    Can you help? My SQL is rusty to say the least. Naturally this would
    be a stored procedure.

    Tom Guest

  3. #3

    Default Re: Generate results based on another collection of results? (users and zip codes)

    Without seeing any DDL this is probably what your after.

    SELECT * FROM Table2 WHERE Table2.Zip IN (SELECT Table1.Zip FROM Table1
    WHERE x)


    --

    Andrew J. Kelly
    SQL Server MVP


    "Jeff 'Jones' Putz" <com> wrote in message
    news:google.com... 


    Andrew Guest

  4. #4

    Default Re: Generate results based on another collection of results? (users and zip codes)

    Ding ding. That's exactly what I needed! Is this an efficient way to do
    it? I assume that the second select essentially makes a giant series of
    "OR" operators internally. Should I not assume that?

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Jeff Guest

Similar Threads

  1. formatting find results in results window or in savedfile
    By InQuistve in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: January 23rd, 03:19 PM
  2. 2 ways to generate PDFs from InDesign give different results
    By Dennis_Dunbar@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 9
    Last Post: October 20th, 09:42 PM
  3. Complex join = no results (for a query that shouldreturn results)
    By jchapman16 in forum Coldfusion Database Access
    Replies: 4
    Last Post: August 23rd, 10:49 PM
  4. Pulling the Verity Collection Name from Results
    By Flyguy1996 in forum Coldfusion - Getting Started
    Replies: 0
    Last Post: August 15th, 07:51 PM
  5. Query results don't display properly in results table.IGNORE PREVIOUS
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: March 24th, 07:28 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139