Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Los Angeles, CA
    Posts
    28

    Unanswered: Query comparing 2 columns

    Here is my problem. I have 2 tables, one has nearly 10 million records, the other about 14,000.
    To simplify it, I have 2 columns in the smaller table that I need to use to “filter” the data from the larger one. Only if neither column matches will the data in the larger table be displayed.

    As an example Both tables contain at least 2 columns, Area Code and Prefix. Table A, the larger one contains multiple instances of every area code and prefix in the nation. (It contains much more data but this is all I am concerned with)
    Table B contains area codes and prefixes that another region uses, and we do NOT serve.

    I need a query to display the data in Table A that we DO serve, basically any thing that matches both columns in table B, do not display.

    I am trying to do this without making a third table for the region we serve, and just run it in a query. But I don’t seem to be having much luck with joining them.

    Any ideas?

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post what you've tried.

    I believe the solution can be done by using MINUS.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Maybe I'm missing something, wouldn't this just be a where not exists...

    select * from ourtable a where
    not exists
    (select 1 from theirtable x where a.areacode=x.areacode and a.prefix=x.prefix);

Posting Permissions

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