Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    52

    Unanswered: Compare 2 columns in 2 tables find difference

    Ive been struggeling with a SQL query in an ASP page with a odbc to a database to try to find new records that comes into the first table(PLANID) and which are not yet in the second (PLANY) table which I update now and then. The columns are identical and have string values.
    Ive tried

    SQL = "select PLANNUMMER From PLANID WHERE not [PLANNUMMER] = (select PLANNUMMER from PLANY)"

    But it seems to only be able to get one record.
    and:

    SQL = "SELECT PLANNUMMER,COUNT(*) FROM PLANID MINUS SELECT PLANNUMMER,COUNT(*) FROM PLANY"

    Does not seem to work either..

    I would be thankful for any ideas
    Thanks//Martin
    "Never underestimate a large number of morons"

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there are three ways to do it:
    PHP Code:
    select PLANNUMMER 
      from PLANID 
     where PLANNUMMER not in
           
    select PLANNUMMER 
               from PLANY 

    PHP Code:
    select PLANNUMMER 
      from PLANID 
     where not exists
           
    select PLANNUMMER 
               from PLANY
              where PLANNUMMER 
    PLANID.PLANNUMMER 
    PHP Code:
    select PLANID.PLANNUMMER 
      from PLANID 
    left outer
      join PLANY
        on PLANID
    .PLANNUMMER 
         
    PLANY.PLANNUMMER
     where PLANY
    .PLANNUMMER is null 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    52

    Talking Re: Compare 2 columns in 2 tables find difference

    Thanks for the fast reply!
    I am a newbee to PHP so.. do I have to use PHP or can it be done in ASP, php is not yet installed on the server..
    Or can I just use the PHP script tag in the ASP page without changing the .asp suffix of the page to the PHP suffix
    something like:

    <script language="php">
    /* SQL = select PLANNUMMER
    from PLANID
    where PLANNUMMER not in
    ( select PLANNUMMER
    from PLANY )*/
    </script>

    Thanks//M
    "Never underestimate a large number of morons"

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am sorry if my response was confusing

    in most discussion forums, [code] ... [ /code ] tags are used to delineate code which is shown using a normal sized, fixed-width font

    in this forum, [ code ] is small sized, proportional font

    therefore i use [ php ] ... [ /php ] to display code, because it displays in the desired font


    you can run those queries directly from ASP
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Posts
    52

    Talking

    ahaa! now I see, That was kind of funny..
    Thanks again!//Martin
    "Never underestimate a large number of morons"

  6. #6
    Join Date
    Mar 2004
    Posts
    52
    It works great! one more question though.. is any of these sql statements quicker than the other?, I am working on a big database with thousands of records in it so the server goes very tired
    //M
    "Never underestimate a large number of morons"

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    theoretically they should all be the same

    but hey, in theory, theory and practice are the same, but in practice, they often aren't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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